counting results of autofilter 2

[electricpete]

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).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

Have you tried to add the column after filtering and then do the sum?

TTFN

 

[iken]

Try using the count function, i.e "=countB1:B20", where your values of 1 in in the range of B1:B20.

 

[francesca]

If you highlight the column, and look to the bottom right hand side of your screen, in the status bar it should read "Sum = " and then the sum of the column. Right clicking here will let you switch between Sum, Count, Count Numbers, Average, Maximum, Minimum and "None".

 

[ivymike]

assuming that your "ones" data is in cells C2:C13, use the following function to sum only the values ("ones") remaining after the filtering is done:
=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()

 

[electricpete]

Thanks everyone.

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.

 

[electricpete]

Mike - your post crossed mine in the mail. Subtotal looks "smarter" than count and sum in terms of ignoring hidden values. Thx.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Just a note to myself - when using Francesca's method I should use the "count" feature and subtract 1. (Count doesn't require numeric data).

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.

 

[jghrist]

If you use SUBTOTAL instead of COUNT or SUM, then the hidden rows are not counted.