Using COUNTA - with autofilter

vlade31

New Member
Joined
Mar 15, 2005
Messages
11
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696

ADVERTISEMENT

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

New Member
Joined
Mar 15, 2005
Messages
11
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696

ADVERTISEMENT

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

New Member
Joined
Mar 15, 2005
Messages
11
Thanks! that works great...now I just have to figure out what that long formula really means :)
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top