Newbe needs help with formula...

[DR, Bob]

I need a easy way to count the following cells with numbers less then 10.

columns c to h, rows 2 to 20, 30 to 40 & 50 to 60 in each column. The next 5
sheets I need to do the same thing but the rows are different on each sheet.

I was going to use
=(COUNTIF(c2:c20,"<10"))+(COUNTIF(c30:c40,"<10"))+(COUNTIF(c50:c60,"<10"))+.
... and so on until I have all the rows and columns covered but the formula
will get very long.

Is there a shorter formula I could use to save from using this very long
formula???

Thanks

 

[Peo Sjoblom]

If the dimensions are identical in the other columns you could use

=COUNTIF(C2:H20,"<10")+COUNTIF(C30:H40,"<10")+COUNTIF(C50:H60,"<10")

or you could select all the ranges on each sheet and give them a unique name
like

MyRange1 for the first sheet and MyRange2 for the second

then use this formula

=SUMPRODUCT(--(LARGE(MyRange1,ROW(INDIRECT("1:"&COUNT(MyRange1))))<10))

for sheet one

 

[Sandy Mann]

Answered in microsoft.public.excel

Sandy

 

[Tom Ogilvy]

if there aren't any numbers in the rows you don't want to count, then you
don't need to exclude them

=countif(C20:H200,"<10")

or if you just want the formula to work on one column

=countif(C20:C200,"<10")