How to Count Unique Values After Filtering Rows in Excel
Question details
The user needs an Excel formula to count unique text values while excluding zeros, ensuring the count dynamically updates when rows are hidden using AutoFilter.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Counting unique records in a dataset where some rows are temporarily hidden or filtered out by the user.
- Observed behavior
- Standard formulas like SUMPRODUCT or COUNTIF calculate the unique values for the entire range, returning incorrect results because they do not ignore rows hidden by AutoFilter.
Ensure your dataset is organized in a clear tabular format without merged cells, and identify the exact column you want to evaluate for unique values.
Use a Visibility-Aware Array Formula (Recommended)
This solution uses a combination of SUBTOTAL, OFFSET, FREQUENCY, and MATCH to actively detect and exclude hidden rows while counting unique values.
To make a formula ignore hidden rows, you must use the SUBTOTAL function. By nesting it with OFFSET, you can evaluate the visibility of each row individually.
When combined with FREQUENCY and MATCH, the formula can successfully identify unique values only within the currently visible data.
Click on the empty cell where you want the dynamic unique count to appear.
Type the following formula, adjusting the ranges (e.g., E9:E34 for the data range, H9:H34 if testing for zeros): =SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(E9,ROW(E9:E34)-ROW(E9),,1)),IF(E9:E34<>0,MATCH(E9:E34,E9:E34,0))),ROW(E9:E34)-ROW(E9)+1),1))
If you are using a version prior to Excel 365, do not just press Enter. Instead, press Ctrl + Shift + Enter to confirm the formula. Curly braces {} will appear around the formula in the formula bar.
If you need to drag this formula across multiple columns, add dollar signs to lock the references (e.g., $E$9:$E$34) before copying.
Count Unique Non-Zero Values (Unfiltered Data)
If you simply want to count unique values excluding zeros on a dataset without active filters, a simpler SUMPRODUCT and COUNTIF formula can be used.
Count Unique Filtered Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array calculations, allowing you to use complex nested formulas like SUBTOTAL, FREQUENCY, and OFFSET to count unique visible values. It is highly compatible with Microsoft Excel formulas.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the data you need to analyze.
- 2. Apply AutoFilter: Highlight your headers, navigate to the 'Data' tab, and click 'Filter' to hide the rows you do not need.
- 3. Select a cell for the result: Click on an empty cell to act as your counter display.
- 4. Enter the array formula: Paste your nested visibility-aware formula (using SUBTOTAL and MATCH) into the formula bar.
- 5. Calculate the unique count: Press Ctrl + Shift + Enter to lock in the array formula and get the accurate count of unique filtered items.
Frequently Asked Questions
Why does my standard COUNTIF formula include hidden rows?
Standard functions like COUNTIF and SUMPRODUCT evaluate all cells within a specified range, regardless of their visual state. To exclude hidden rows, you must use functions explicitly designed to evaluate visibility, such as SUBTOTAL or AGGREGATE.
How can I count unique values in Excel 365 without complex array formulas?
In Excel 365, you can avoid complex older array formulas by combining the new UNIQUE and FILTER functions. However, to exclude manually filtered rows, you still need to incorporate a helper column or the SUBTOTAL function within the FILTER criteria.
What is the difference between SUBTOTAL 3 and SUBTOTAL 103?
Both codes perform a COUNTA operation. However, code 3 only ignores rows hidden by a filter, while code 103 ignores rows hidden by a filter AND rows that were manually hidden by right-clicking and selecting 'Hide'. Using 103 is generally safer for excluding all non-visible data.
