×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# counting results of autofilter2

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

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

### RE: counting results of autofilter

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

TTFN

### RE: counting results of autofilter

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

### RE: counting results of autofilter

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

### RE: counting results of autofilter

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

### RE: counting results of autofilter

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

### RE: counting results of autofilter

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

### RE: counting results of autofilter

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

### RE: counting results of autofilter

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!