How to Show a Unique Count in Excel with a Formula

Formulas & functions · Updated November 18, 2026

To count distinct nonblank values in Excel for Microsoft 365, put your data in A2:A100 and enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) in an empty cell. The result updates when the values change. This guide explains the formula, a PivotTable option, and practical alternatives for older Excel versions. A unique count counts each different value once; it does not count how many times each value appears.

What You’ll Need

  • A column or row of values to count. A heading is fine, but keep it outside the range you count.
  • An empty cell for the result. If you use the formula method, Microsoft 365 or Excel 2021 is required for UNIQUE and FILTER.
  • For the PivotTable method, use Excel desktop for Windows and be prepared to add the source data to the Data Model. Data Model features vary by platform; the formula method is a better choice if you use Excel for Mac or Excel for the web.

Step-by-Step

  1. Identify the values you want to count. Decide whether the count should cover one column, one row, or a particular part of a list. For example, if product names are in column A and the heading is in A1, the data may run from A2 to A100. Use a range that excludes the heading so Excel does not count it as a value. If you expect the list to grow, consider using an Excel Table so the source range can expand with new rows.
  2. Choose how to treat blanks. The formula in this guide ignores genuinely empty cells and formulas that return an empty string. That is usually what people want when counting categories, names, or labels. A cell containing a space is not empty, however, and will count as a distinct value. If an empty entry should count as a category in your report, use a method that includes blanks rather than the formula below.
  3. Enter the dynamic formula. With the source values in A2:A100 and the result cell outside that range, type this formula into the result cell and press Enter: =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). It returns zero if there are no nonblank values, rather than displaying an error. Replace A2:A100 in both places with your actual data range. Keep the range endpoints consistent so the formula filters and counts the same cells.
  4. Check the result against a small sample. Suppose A2:A11 contains North, South, North, East, a blank cell, South, West, West, North, and a blank cell. With those values in A2:A11, the formula is =IFERROR(ROWS(UNIQUE(FILTER(A2:A11,A2:A11<>""))),0). It returns 4: the distinct nonblank entries are North, South, East, and West. The repeated North, South, and West entries do not increase the count.
  5. Understand what each function does. FILTER keeps entries that are not blank. UNIQUE reduces the remaining entries to one instance of each value. ROWS counts how many entries are in that unique result. IFERROR returns 0 if the range has no qualifying values. The formula produces a number in one cell; it does not display the list of unique entries. If you also want to see the list, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in a separate empty area. With data in A2:A100, that formula spills the distinct nonblank values into the cells below.
  6. Use a PivotTable when you need a report. In Excel for Windows, select a cell in your source data and choose Insert > PivotTable. In the dialog, select the range or table, choose where to put the PivotTable, and check Add this data to the Data Model before you create it. In the PivotTable Fields pane, drag the field you want to count into Values. This method is useful when you also want to group the unique count by another field, such as distinct customers by region.
  7. Change the PivotTable calculation to Distinct Count. In the PivotTable, open the drop-down for the value field and choose Value Field Settings. Under Summarize Values By, select Distinct Count, then choose OK. If Distinct Count is missing, the PivotTable was probably created without the Data Model; create it again and check the Data Model option. The PivotTable is a report, so changes to its source data may require you to right-click inside it and choose Refresh.
  8. Choose a method for older Excel versions. UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 and earlier. For a one-time count in an older version, copy the source values to an unused helper column, select the copied values, and choose Data > Remove Duplicates. Confirm the column selection, then select the remaining values below the heading. The Count value on Excel’s status bar gives the number of nonblank entries. Work on the copy, not the original data, because Remove Duplicates changes the selected cells.

Common Mistakes

Including a heading in the range: If you count A1:A100 when A1 says Region, Excel treats Region as a value and adds it to the count. Start at A2 when row 1 is a heading. For a table, select the data column rather than its header when building a formula.

Counting occurrences instead of distinct values: COUNT counts numeric cells and COUNTA counts nonempty cells; neither by itself counts each repeated category just once. For example, a list with five rows can have only three distinct labels. Use UNIQUE with a count of its results, or configure a PivotTable for Distinct Count.

Assuming differently capitalized text is different: Excel’s UNIQUE function does not distinguish values only by letter case in its normal use. Entries such as north and North are generally treated as the same value for this count. If capitalization must define separate categories, standardize or transform the data first, or use a specifically case-sensitive approach.

Overlooking spaces and inconsistent spelling: North, North followed by a space, and Noth are separate text values. A unique count can therefore be higher than expected even though some entries look alike at a glance. Clean the source values before counting if they should represent the same category.

Putting the formula inside its source range: Keep the result cell outside A2:A100. If the formula is included in the range it reads, it can create a circular reference or cause confusing results. Also make sure there is clear room below a separate UNIQUE list for its results to spill.

Tips

Use an Excel Table for a list that changes often. Select the source data and choose Insert > Table, making sure the heading option is correct. Tables expand when you add rows, which reduces the chance that a fixed range such as A2:A100 will miss newly added entries. Structured references can make formulas easier to read, but use a normal cell range if you are not familiar with table reference syntax.

Keep the raw data intact and do cleanup in a helper column or a separate copy. If extra spaces are the issue, use a helper column with TRIM to remove leading and trailing ordinary spaces, then count the cleaned results. TRIM does not remove every possible invisible character, such as some nonbreaking spaces copied from websites, so imported data may need additional cleaning.

Place a descriptive label next to the result, such as Distinct regions, and format the result cell as General or Number. The formula returns a count, not a percentage. If you copy the formula to another worksheet, confirm that the source reference points to the intended sheet and range.

For a PivotTable report, put the grouping field, such as Region, in Rows and the field to count, such as Customer ID, in Values. Set the value calculation to Distinct Count. This shows one distinct customer count per region, while the formula method described above counts distinct values across its specified range as a whole. After adding, removing, or editing source records, refresh the PivotTable so the report reflects the updated data.

Excel for Mac and Excel for the web support the dynamic array formula functions in current versions, though menus and dialog layouts can differ slightly. The keyboard shortcut for entering a formula is Enter on Windows and Return on Mac. If a spill formula is used to display a list, do not type into the cells where its results need to appear.

Troubleshooting

The formula returns #NAME? or #CALC!: Check that your Excel version supports UNIQUE and FILTER. These functions require Microsoft 365 or Excel 2021 or later. If the formula is supported but the range contains no nonblank entries, the IFERROR wrapper should return 0. If you removed that wrapper, an empty FILTER result can produce #CALC!.

The answer is larger than expected: Inspect the source range for spaces, inconsistent spelling, or an accidentally included heading. Select suspicious cells and look in the formula bar; a trailing space may not be obvious in the worksheet. You can also sort or filter the source data to bring similar entries together before cleaning them.

The answer is smaller than expected: Check whether two labels differ only in capitalization, since the standard unique count treats case-only differences as the same. Confirm that the range includes every intended row and that numbers stored as text or dates are represented consistently. Also check whether blank-looking cells are formulas returning an empty string; this guide’s formula deliberately excludes them.

The PivotTable does not offer Distinct Count: Recreate the PivotTable and check Add this data to the Data Model in the creation dialog. If you are using a platform or Excel edition where the Data Model option is unavailable, use the dynamic formula in a supported version or the copy-and-remove-duplicates method. A normal PivotTable count is not automatically a distinct count.

The formula shows a spill-related problem: The main formula returns a single number, so a blocked spill range is not normally the cause. If you entered the optional UNIQUE list formula, clear any contents or merged cells in the area where the list needs to appear, then re-enter it. A separate list needs enough empty cells below the formula to show all results.

Frequently Asked Questions

What is the difference between a unique count and a regular count?

A regular count totals cells or entries, including repeated values depending on the function used. A unique count totals different values only once. If a list contains Red, Blue, Red, its regular number of entries is 3 and its distinct count is 2.

Does the formula count blank cells?

No. The FILTER condition removes empty cells and formulas that return an empty string. A cell containing a space is not technically blank and can count as a distinct value. Clean spaces from the data if they should be ignored.

Can I count unique values across more than one column?

Yes, but decide whether you mean distinct entries from either column or distinct combinations of values on each row. Those are different questions. UNIQUE can work with a multi-column range to return distinct rows, but the simple formula here is designed for one column. Arrange or combine the fields deliberately before counting combinations.

Will the count update when I add data?

It updates when values within the formula’s referenced range change. A fixed reference such as A2:A100 does not include a new value entered in A101. Use an Excel Table or extend the formula’s range if the list may grow. PivotTables need to be refreshed after source changes.

Can Excel count North and north as two distinct values?

Not with the standard UNIQUE formula shown here; it does not treat a capitalization difference alone as a separate value. If case matters, create a case-sensitive method or normalize the source values according to the reporting rule you need before counting.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.