Need to total on a Countall variable 1

[kward123]

I have a query that shows as follows:

aaa 111
aaa 123
aaa 234
Countall variable = 3

bbb 222
bbb 234
Countall variable = 2

Question how can I add a total count of only those Countall variables that have 3 or more?

Any help would be greatly appreciated !!!

Thank you.

 

[skrandel]

Create a break on your first column. Let's say it's Region
Let's say your 2nd column is City.

Here's the final formula for the Grand Total:
=Sum(If( CountAll(<City>) In <Region> >=3) Then 1 Else 0)

Break it down:
CountAll(<City>) In <Region> Gives you the count within each break. You need to explicity specify the output context so that it doesn't change when you move it to the footer

The if then else part is being used to generate a flag so that each region gets a 1 or a 0 depending on if it qualifies

The sum just adds up the flags. Adding up 1's and 0s is the same as counting 1's

Let me know if this works.



Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com