Countif excluding hidden rows

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

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,OF FSET($K$2,ROW($A$2:$A$500)-MIN(ROW($A$2:$A$500)),,))))


for 1 or 2



--


Regards,


Peo Sjoblom

"qh8519a" wrote in message
...
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

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)

--
Biff
Microsoft Excel MVP


"qh8519a" wrote in message
...
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

Ooops!

I see you wanted a COUNT not a sum.

Change the formula in CV8 to:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"qh8519a" wrote in message
...
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

Well DUH!

LOL!

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

Disregard my previous reply.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ooops!

I see you wanted a COUNT not a sum.

Change the formula in CV8 to:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"qh8519a" wrote in message
...
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