Excel’s SUBTOTAL function cannot count unique values by itself. In Microsoft 365, combine UNIQUE and FILTER with SUBTOTAL to count distinct, nonblank values in the rows that remain visible after filtering. For a list in A2:A100, the formula is =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))))),0).
What SUBTOTAL can and cannot do
SUBTOTAL calculates a summary for a range, and it can respond to filtered or hidden rows. Its function number determines the calculation: for example, 2 counts numbers, while 3 counts nonempty cells. The corresponding numbers 102 and 103 perform those counts while also ignoring rows hidden manually. Rows excluded by an AutoFilter are ignored by both sets.
None of these options means “count each different value only once.” If a visible list contains North, North, East, the ordinary count of nonblank cells is three, but the distinct count is two. This is why changing SUBTOTAL from 3 to 103 does not solve a unique-count problem: it changes which rows are considered, not how duplicate values are treated.
The distinction matters in reports. You might want to count visible customer names after filtering a sales table, or count the different products shown for one region. A standard subtotal can count the visible records, but a distinct count needs an additional method.
Choose a method for your task
Use a formula when the result should update as users filter worksheet rows and you want the answer in a normal cell. Use a PivotTable when you want a reusable summary with fields that readers can rearrange. If you simply need a list of distinct values, rather than their count, use UNIQUE on its own or with FILTER.
- Visible worksheet rows: Combine
SUBTOTALwithFILTERandUNIQUE. - All values, regardless of filters: Use
UNIQUEand count its result. - PivotTable report: Add the source data to the Data Model and select the Distinct Count summary.
- Excel without dynamic-array functions: Consider a PivotTable with Distinct Count, or a helper-column approach suited to that workbook.
These methods answer related but different questions. Decide whether hidden rows should count and whether you need a displayed list, a worksheet formula, or a report before choosing.
Formula for unique values in visible rows
Assume the values to count are in A2:A100, with a header in A1. Enter this formula in a cell outside the filtered list:
With values in A2:A100, this formula counts distinct nonblank values in rows visible under a worksheet filter and also excludes rows hidden manually:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))))),0)
The formula returns a single number. It requires Microsoft 365 or Excel 2021 or later for FILTER and UNIQUE. The inner SUBTOTAL test creates a visible-row check for each cell; FILTER keeps only qualifying values; UNIQUE removes duplicates; and ROWS counts the resulting list. IFERROR returns zero if the filter finds no qualifying values.
The formula uses OFFSET to build a reference for each row, with ROW supplying the row offsets from A2. Because OFFSET is a volatile function, Excel recalculates it whenever the workbook recalculates. That is usually acceptable for a modest list, but a very large workbook with many such formulas may recalculate more slowly.
Set up and use the formula
- Put the field you want to count in one column, with a single header above it. Keep the data range consistent; this example uses A2:A100.
- Apply a filter using Data > Filter, then choose the criteria that determine which worksheet rows remain visible.
- Enter the formula in a cell outside the filtered range so the result is not itself filtered along with the data.
- Change the filter and check that the result changes to reflect the newly visible records.
- If new records may extend beyond row 100, expand the formula range or convert the source to an Excel table and adapt the references carefully.
On Windows, Ctrl+Shift+L toggles filters for a selected range or table. On Mac, the shortcut is Command+Shift+F in current Excel versions. You can also use the Data tab’s Filter command, which avoids relying on a keyboard shortcut when the selected range is unclear.
If your formula separators are semicolons rather than commas, your regional settings may require semicolons in formulas. The formula shown here uses standard comma separators. Do not include the header in the counted range, or the header text may be treated as another distinct value.
Example: a filtered product list
Suppose A2:A8 contains North, South, North, East, a blank cell, South, and West. Before filtering, the distinct nonblank values are North, South, East, and West, so a formula that counts all rows returns four. If a filter leaves only the first North, the second North, East, the blank, and West visible, the visible distinct count is three: North, East, and West. Repeated North counts once, and the blank is excluded.
This example also shows why a simple visible-row count is not the requested answer. There are four visible records, but only three different nonblank values. If the task were to count records rather than distinct items, SUBTOTAL(3,A2:A100) could be appropriate; it would not deduplicate the names.
The count is based on the values Excel compares in the cells. If source data contains inconsistent spelling, extra spaces, or different representations such as a number stored as text, the result may not match a person’s idea of one category. Clean or standardize the source values when those differences should be treated as the same item.
Count unique values without considering filters
If filters and hidden rows should have no effect, use a simpler dynamic-array formula. With the values in A2:A100, this returns the number of distinct nonblank values in the entire range:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)
This formula does not check row visibility, so applying a worksheet filter will not change its answer. To see the unique values themselves instead of counting them, use =UNIQUE(FILTER(A2:A100,A2:A100<>"")) with the same range layout. The results spill into adjacent cells, so leave enough empty space below the formula.
One frequent misunderstanding is the difference between distinct values and values that occur exactly once. UNIQUE returns one copy of each different value by default, even if it appeared many times. Its optional exactly-once setting can instead return only items that occur once. That is a different question: in the example, North and South would not qualify because each appears twice.
PivotTable alternative for a distinct count
A PivotTable can provide a distinct count without writing a dynamic-array formula. In Microsoft 365 for Windows, select the source data and choose Insert > PivotTable. In the PivotTable creation dialog, select Add this data to the Data Model, then create the PivotTable. Add the field you want to count to the Values area, open its value-field settings, and choose Distinct Count under the summary options.
Distinct Count is available for PivotTables using the Data Model; if you do not add the data to the model, the option may not appear. A PivotTable is especially useful when the report needs categories in rows or columns as well as a distinct count. Its filters and slicers control the PivotTable report, which is separate from filtering ordinary worksheet rows for the formula above.
Excel for Mac has PivotTables, but the dialogs and available Data Model capabilities can differ by Excel version. If the Data Model option or Distinct Count summary is missing, use the worksheet formula in a supported Microsoft 365 version or check the feature availability for your installed Excel edition. Excel for the web also has version-dependent support for creating and editing PivotTable features; the formula method is often easier to verify there.
Common problems and checks
- The count does not change after filtering: Confirm that the formula covers the filtered cells and that the filter is a worksheet AutoFilter, not just a visual selection or a separate PivotTable filter.
- The answer is one when there are no matching values: Check that the formula includes the
IFERRORwrapper and excludes blanks. If you change the formula, test it with an empty result. - The result is too large: Make sure the header is not included and check for spaces or inconsistent source entries. A blank cell is excluded by the formula, but a cell containing spaces is not blank.
- Manually hidden rows are handled differently than expected: Function number
103ignores manually hidden rows as well as filtered-out rows. Replace it with3inside the formula if manually hidden rows should still count while filtered-out rows remain excluded. - The formula returns an error: Check that the range sizes match, that the formula uses commas for your regional settings, and that the Excel version supports
FILTERandUNIQUE.
To use function number 3, replace only the 103 argument inside SUBTOTAL; do not change the surrounding formula structure. Both 3 and 103 ignore rows removed by a filter. The difference is whether rows hidden manually are included.
Frequently Asked Questions
Can SUBTOTAL count unique values directly?
No. It can count visible numbers or nonempty cells, but it has no built-in distinct-count option. Combine it with functions such as FILTER and UNIQUE, or use a PivotTable with Distinct Count.
Does the formula count blank cells?
No. The A2:A100<>"" test excludes genuinely blank cells. Cells containing spaces or other characters are not blank and may be counted as a distinct value.
Will the result update when I change a filter?
Yes. The SUBTOTAL visibility check responds to worksheet filters, and Excel recalculates the formula. It will not respond to a filter applied only within a separate PivotTable report.
What is the difference between unique and exactly once?
A distinct count includes one count for every different value, even if it repeats. An exactly-once count includes only values that appear a single time in the source. Those measures are not interchangeable.
Can I use this formula in older Excel?
The displayed formula needs FILTER and UNIQUE, available in Microsoft 365 and Excel 2021 or later. Older versions can use a Data Model PivotTable with Distinct Count where supported, or a helper-column method designed for that version.
Bottom line
For a distinct count that changes with worksheet filters, use SUBTOTAL as the visibility test and let FILTER and UNIQUE do the filtering and deduplication. Use function number 103 to exclude both filtered-out and manually hidden rows, or 3 if manually hidden rows should remain included. For a report rather than a worksheet formula, a Data Model PivotTable with Distinct Count is a practical alternative.
Related
- How to Hide Columns in Excel for the Web: Steps & Tips
- How to Hide Columns in Excel Only for You Using Sheet Views
- Hide Columns in Excel on SharePoint Without Deleting Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Function: Find a Value’s PositionExcel’s MATCH function finds a value in a range and returns its relative position, not the value itself.…
- Excel Color Filter: How to Filter Rows by Cell ColorTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel Date Difference in Months: Formulas That WorkTo find the number of complete months between two dates in Excel, use =DATEDIF(A2,B2,"m"), with the earlier date…
- Basic IF Examples in Excel: How to Write the FormulaIn Excel, an IF formula checks whether a condition is true and returns one value if it is,…
- Excel Consolidate Function: How to Combine DataTo use Excel’s Consolidate function, select the top-left cell for the results, then choose Data > Consolidate, select…
- VLOOKUP in Excel: Return a Value from Another CellTo make VLOOKUP return a value from another cell, look up a key in the leftmost column of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.