Using COUNTA - with autofilter

[vlade31]

Hey guys,

I am using the COUNTA to count nonblank cells within a range, which works just fine..however when i use the autofilter to filter out some of my data...my COUNTA cells stays the same..is there some way to count only what i am displaying at the time..Thanks in advance

 

[Richard Schollar]

Hi Vlade

Use:

=SUBTOTAL(3,YourRange)

 

[vlade31]

Hi Vlade

Use:

=SUBTOTAL(3,YourRange)

THANKS!!! YOU ARE MAN!!!

you just made me look good in front of my boss..

Thanks.

 

[vlade31]

Is there a way to use the COUNTIF and SUBTOTAL functions together?

 

[Domenic]

To count the number of cells, in a filtered list, which are not empty, try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(Range<>""))

Hope this helps!

 

[vlade31]

Actually all I am trying to do is a simple COUNTIF..for example =countif(range,"text")....but i still wanna be able to use the autofilter functions...so basically i want countif to only count whats being displayed and return results accordingly...is that do able?

 

[Domenic]

To count the number of cells, in a filtered list, which equals a specified text, try changing this part of the formula...

--(Range<>"")

to

--(Range="text")

Hope this helps!

 

[vlade31]

Thanks! that works great...now I just have to figure out what that long formula really means

 

[Domenic]

Thanks! that works great...

You're very welcome!

now I just have to figure out what that long formula really means

Have a look here...

 

[vlade31]

Thanks