I have a group of data with autofilters on headers I would like to count
occurances of numbers by using countif. When I select filtered criteria I
would like the values to change. I can't make this work with a pivot table
because of the structure of the data. Is there a countif visible formula
similar to a subtotal(9,AB1:AB23456)?

Thanks

Yes - use 109 instead of 9
=SUBTOTAL(109,...)
or 101, 102, etc.
"Sean" wrote in message
...
I have a group of data with autofilters on headers I would like to count
occurances of numbers by using countif. When I select filtered criteria I
would like the values to change. I can't make this work with a pivot table
because of the structure of the data. Is there a countif visible formula
similar to a subtotal(9,AB1:AB23456)?

Thanks

"Sean" escreveu:

I have a group of data with autofilters on headers I would like to count
occurances of numbers by using countif. When I select filtered criteria I
would like the values to change. I can't make this work with a pivot table
because of the structure of the data. Is there a countif visible formula
similar to a subtotal(9,AB1:AB23456)?

Thanks


=subtotal(2,ab1:ab23456)

the 2 is for count
take a look on subtotal help

hth
regards from Brazil
Marcelo

Yes; to COUNT visible only, use 102
"Marcelo" wrote in message
...



"Sean" escreveu:

I have a group of data with autofilters on headers I would like to count
occurances of numbers by using countif. When I select filtered criteria
I
would like the values to change. I can't make this work with a pivot
table
because of the structure of the data. Is there a countif visible formula
similar to a subtotal(9,AB1:AB23456)?

Thanks


=subtotal(2,ab1:ab23456)

the 2 is for count
take a look on subtotal help

hth
regards from Brazil
Marcelo

To count within a filtered list the number of times a cell in B2:B100
equals a certain criteria, try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B1
00=Criteria))

....where the criteria is a numerical value. Adjust the range
accordingly.

Hope this helps!

In article ,
Sean wrote:

I have a group of data with autofilters on headers I would like to count
occurances of numbers by using countif. When I select filtered criteria I
would like the values to change. I can't make this work with a pivot table
because of the structure of the data. Is there a countif visible formula
similar to a subtotal(9,AB1:AB23456)?

Thanks