Microsoft

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

In this week's Office challenge, tests your Excel skills by providing a solution that can count filtered rows.

Last week's challenge asked for a dynamic counting function. The CountIf() function we used works fine, unless you decide to filter the set and you want a count of the results! The CountIf() evaluates the entire data set, not a filtered set. Using last week's sheet, how would you return the count of a filtered set? You can use any feature you want - no restrictions. This doesn't have to be hard - and there's your clue!

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.

4 comments
mvdarend
mvdarend

add the total row and use the count function in the appropriate column to count nr of rows after filtering.

martin.berghman
martin.berghman

In most cases I see the count of the visible records in the status-bar. However in some other cases the status-bar only shows "Filter mode"

Peduncle
Peduncle

As Iceberg says, assuming you want to count the number of rows shown when you've filtered (use a Custom filter to select something like Sold < 75) ... Right click on the status bar and pick Count Drag down to highlight a column of cells in your filtered set The status bar tells you how many rows you have.

IcebergTitanic
IcebergTitanic

You would use the Subtotal function rather than the countif function to perform calculations on a filtered set.. In Cell A4: =subtotal(2,$B$6:$B$13) This assumes you have filtered your set to show only the things you want to count. The #2 in the function is what operation you perform on the visible cells. Operation 2 is Count.

Editor's Picks