You can use the SUBTOTAL function to count visible items in a filtered list. In today’s example, AlexJ shows how to count the unique visible items in a filtered list. So, if an item appears more than once in the filtered results, it would only be counted once. Thanks, AlexJ!
Count the Unique Items
In the screenshot below, the formula in column D shows a 1 for the first instance of each item, and a 0 for all subsequent instances. For example, in cell D7, there’s a 1, counting item 91-AB145, and in the next two rows the count for the same item is zero.
The formula in cell D5 is:
Apply a Filter
However, when the list is filtered to show only the Central region items, the total in cell D2 still shows 16 unique items, and cell D11 shows a zero for item 91-AB146, even though it’s the first visible instance of that item.
To solve the problem, AlexJ added some helper columns to the table. First, in column E, he created a formula to check if the row is visible. The formula in cell E5 is:
AlexJ uses the 1 multiplier at the start of the formula to avoid the problem of the last row not being included in the AutoFilter range.
Based on this new formula, the total in cell E2 correctly shows that only 6 rows are visible.
Test For Visible Rows
Next, in column F, AlexJ created a formula to show the item name if the row is visible. For hidden rows, the formula displays a hyphen instead of the item name. In cell F5, the formula is:
In cell G1, I tested the result for hidden cell F6, and you can see the result is a hyphen.
Count Unique Visible Items
Finally, in column G, AlexJ created a formula to check for unique items in column F, where only the visible rows have an item name. In cell G5, the formula is:
With this formula, the hidden rows are ignored, and the count of unique items in cell G2 is correct for the filtered rows. The item 91-AB145 is counted only once, even though it’s in the filtered results twice, and item 91-AB146 is counted, even though it’s not the first instance of that item in the full list.
Array Formula to Count Unique
Another way to count unique items in a filtered list, is with named ranges and an array formula, as described in the July 2001 issue of Excel Experts E-letter (EEE), by David Hager. David’s formula is in cell F3 below, and shows the same results as AlexJ’s formula in cell G3.
Make sure you do a few warm up stretches before you attempt this one!
Download the Sample File
To see AlexJ’s formulas, you can download Alex’s sample file from the Contextures website. On the AlexJ Sample Files page, go to the Filters section, and look for: FL0001 – Count Unique Items in Filtered List