Function Problems

How to Count Unique Visible Values Excluding Hidden Rows in Excel

Camila Milosovich Oct 12, 2026 871 views

Question details

The user needs a method to count distinct product groups or items in a dataset without counting rows that have been manually hidden or filtered out.

How to Count Unique Visible Values Excluding Hidden Rows in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing a filtered dataset where standard counting formulas inaccurately include hidden rows in the final count of unique items.
Observed behavior: Standard functions like UNIQUE or COUNTIF evaluate the entire range, inadvertently including hidden or filtered rows in the distinct count.

Before you start

Verify your Excel version, as the optimal solution depends on whether you have access to modern Dynamic Array functions (Microsoft 365, Excel 2021+) or are using an older version like Excel 2019.

Solution 1Recommended

Use Dynamic Arrays in Modern Excel

Utilize UNIQUE, FILTER, BYROW, LAMBDA, and SUBTOTAL for a robust, single-cell formula solution in newer versions of Excel.

This approach leverages modern Dynamic Array capabilities. By nesting SUBTOTAL inside a LAMBDA function handled by BYROW, Excel calculates the visibility of each row individually before filtering and counting the unique values.

Select the destination cell

Click on the cell where you want the final distinct count to appear.

Enter the modern array formula

Type the formula: =COUNTA(UNIQUE(FILTER(A2:A11, BYROW(B2:B11, LAMBDA(a, SUBTOTAL(103, a)))))) adjusting ranges A2:A11 (criteria) and B2:B11 (visibility check column) to match your dataset.

Calculate the result

Press Enter. The formula automatically filters out rows where SUBTOTAL returns 0 (hidden), leaving only unique visible values to be counted by COUNTA.

Understanding SUBTOTAL 103: Using the 103 argument in SUBTOTAL ensures that both filtered rows and manually hidden rows are ignored.

Solution 2

Use OFFSET and SUBTOTAL for Legacy Excel

Use a complex array formula utilizing SUM, SUBTOTAL, and OFFSET to calculate unique visible values in Excel 2019 and older versions.

Solution 3

Use a Helper Column Method

Add a helper column to identify row visibility, significantly simplifying the final counting formula.

Easily Analyze Filtered Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, SUBTOTAL calculations, and intuitive filtering tools, allowing you to accurately count unique visible values seamlessly without complex workarounds.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your filtered dataset.
2. Select the target cell: Click on the cell where you wish to display the count of unique visible values.
3. Apply the formula: Enter the required array formula, such as =SUM(SUBTOTAL(...)), or utilize a helper column for straightforward counting.
4. Get instant results: Press Enter to execute the formula and instantly view your accurate data summary.

100% compatible with Microsoft Excel formulas including SUBTOTAL and OFFSETAdvanced filtering capabilities for quick and accurate data analysisLightweight, fast execution for large datasetsFamiliar interface with zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard UNIQUE function include hidden rows?

The standard UNIQUE function in Excel processes the entire specified array regardless of whether the rows are visually hidden or filtered. To exclude hidden rows, the array must first be filtered using functions like SUBTOTAL that actively detect row visibility.

What is the difference between SUBTOTAL 3 and 103?

In the SUBTOTAL function, argument 3 (COUNTA) ignores rows that are hidden by a filter but includes rows that are manually hidden by the user. Argument 103 ignores both filtered rows and manually hidden rows, ensuring strictly visible cells are evaluated.

Can I count unique visible values using a Pivot Table instead?

Yes. If you check the 'Add this data to the Data Model' box when creating a Pivot Table, you unlock the 'Distinct Count' summarization option. Filtering the Pivot Table will automatically adjust the distinct count to include only the visible items.

Will these array formulas slow down my Excel workbook?

Formulas utilizing OFFSET are volatile, meaning they recalculate every time any change is made in the workbook. In very large datasets, this can cause performance drops. The modern Dynamic Array approach (UNIQUE/FILTER/BYROW) is generally more efficient.

Excel & Data Problems