Software

Solution: How do you count the results of a filtered set?

Read the easy solution to last week's Office Challenge: how to count items in a filtered set. Have you got a better solution? Let us know about it.
Last week, we asked you to count items in a filtered set. Your only clue was that the solution doesn't have to be hard - and Peduncle caught right on.

The easiest way is to select the values and check the status bar. Although it's difficult to see in the sheet shown below, I've selected two cells - the two corresponding to Bill - and the status bar count function displays 2. Excel will evaluate selected values (two or more) and displays the results in the status bar. You can even customize the bar's display by right-clicking it. Martin.berghman also suggested the status bar functions.

That method is easy, but it's a one-time deal. If someone asks you in a meeting to evaluate a subset, you can quickly select the subset and read the result right from the status bar. They'll think you're a genius.

There's another easy solution that no one mentioned - AutoSum. After filtering a data range, click AutoSum and Excel will enter a sum. Then, click the result cell's dropdown arrow and choose Count! It's a bit more permanent and it updates automatically as you change the filter!

IcebergTitanic suggested using Subtotal(), which the AutoSum enters for you with just a few clicks. Or, use the Subtotal feature if you haven't turned the data range into a table. The example sheet I used displays a table - a new feature in Excel - and you can't use the Subtotal feature with tables.

Thanks for another great challenge!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments

Editor's Picks