Summing multiple ranges based on criteria....

Hi,

I thought i'd cracked this but my formula doesn't seem to be working correctly, and it's a bit unwieldy (ie long!), so im hoping someone can offer a better solution...

I have 9 columns of numbers: A9:A200 across to: I9:I200. I have to work out the total number of values in those columns that fit the following criteria:

<40
40-49
50-59
60-69
70-79
80+

This is slightly complicated by the fact that should the value 39v appear in any of the columns, should be counted alongside the other numbers that are<40. Anyone any suggestions?

try using countifs for first condition would be
=COUNTIFS($A$9:$I$200,">"&0,$A$9:$I$200,"<"&40)

to Count in individual columns you could do
=COUNTIFS(A$9:A$200,">"&0,A$9:A$200,"<"&40) and drag to right

I'd add the 39v to this by:
=COUNTIF($A$9:$I$200,"<40") +COUNTIF($A$9:$I$200,"39v")
for next ranges:
=COUNTIFS($A$9:$I$200,">=40",$A$9:$I$200,"<50")
etc
and the last one again countif is enough:
=COUNTIF($A$9:$I$200,">=80")

as for individual columns - follow changes by hemesh (instead of $A$9:$I$200 -> A$9:A$200) in above formulas