counting results of autofilter
counting results of autofilter
(OP)
I do a lot of work with autofilter.
Is there a quick easy way to determine how many rows are displayed for any selected autofilter criteria.
If I add a column with 1 and do count or sum, it also includes the hidden rows (the rows not selected by autofilter criteria).
Is there a quick easy way to determine how many rows are displayed for any selected autofilter criteria.
If I add a column with 1 and do count or sum, it also includes the hidden rows (the rows not selected by autofilter criteria).
=====================================
Eng-tips forums: The best place on the web for engineering discussions.





RE: counting results of autofilter
TTFN
RE: counting results of autofilter
RE: counting results of autofilter
RE: counting results of autofilter
=SUBTOTAL(9,C2:C13)
use the following to count the non-blank cells left after filtering:
=SUBTOTAL(2,C2:C13)
see the help for more on subtotal()
RE: counting results of autofilter
The count and sum don't work if I have placed the 1's into the column before the filter. i.e. count and sum still count the 1's that are hidden by being filtered out. Only if I specifically copy 1's into the column AFTER I filter, then it works. But that is a lot of work if I want to check numbers for several different filter combinations. And if I don't clean up my 1's for the next filter I will be wrong.
For my particular need, francesca's tip is just right. No need to create any extra data, minimum number of keystrokes. And it only counts the items that are screened in (not the ones that are hidden). I just have to remember to subtract 1 from the total because I have headings on the top of my data.
Thx again.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: counting results of autofilter
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: counting results of autofilter
Sorry for cluttering up eng-tips with notes to myself. But I'll probably never remember it otherwise. It's hell getting old.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: counting results of autofilter