Countif excluding hidden rows

[qh8519a]

I would like to count the total number of "1"s and "2"s across a range of
(b8:cu30000). I have an autofilter set up and I would only like to count the
exposed rows. I would like to exclude the rows hidden by the autofilter.
When I use the countif function, it includes the hidden rows. Any help would
be appreciated.

Thanks
Drew

 

[Peo Sjoblom]

If you filter and only want to count visible rows you can use

=SUBTOTAL(3,A2:A500)

If you want some extra criteria for the visible cells


=SUMPRODUCT(--($A$2:$A$500=1),(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$K$500)-MIN(ROW($A$2:$$500)),,))))

for 1


=SUMPRODUCT(--(($A$2:$A$500=1)+($A$2:$A$500=2)>0),(SUBTOTAL(3,OFFSET($K$2,ROW($A$2:$A$500)-MIN(ROW($A$2:$A$500)),,))))


for 1 or 2



--


Regards,


Peo Sjoblom

 

[T. Valko]

For that big of a range you should add a column that counts those values by
row.

Enter this formula in CV8 and copy down to CV30000:

=SUM(COUNTIF(B8:CU8,{1,2}))

Then:

=SUBTOTAL(9,CV8:CV30000)

 

[T. Valko]

Ooops!

I see you wanted a COUNT not a sum.

Change the formula in CV8 to:

=--(SUM(COUNTIF(B8:CU8,{1,2}))>0)

 

[T. Valko]

Well DUH!

LOL!

I'll get it right one of these times (I hope!)

Disregard my previous reply.