Count Unique Values in Excel: Formulas and Methods

Formulas & functions · Updated November 19, 2026

To count distinct, nonblank values in Excel for Microsoft 365, use =ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""))). This assumes the values are in A2:A20; it counts each different value once, even if that value appears repeatedly.

What “unique” means in Excel

People use “unique” to mean two related but different things. A distinct value is one that differs from the others in the list, counted once regardless of how many times it appears. A value that occurs exactly once appears in only one row. Excel’s UNIQUE function can return either kind, so decide which count you need before choosing a formula.

For example, consider a list containing Red, Blue, Red, and Green. There are three distinct values: Red, Blue, and Green. But only Blue and Green occur exactly once, so there are two values that appear once. This distinction matters for tasks such as counting customer names versus finding one-off transactions.

The opening formula filters out blank cells, creates a list of distinct remaining values, and counts the rows in that list. If A2:A20 contains Red, Blue, Red, Green, and blanks, it returns 3. If blank cells should count as one of the distinct values, do not filter them out; whether that is useful depends on what a blank represents in your data.

Count distinct nonblank values with UNIQUE

In Microsoft 365 and Excel 2021 or later, UNIQUE returns an array containing the different entries in a range. FILTER removes blanks first, and ROWS counts the resulting list. The formula spills its intermediate list internally, but only the count appears in the cell containing the formula.

With values in A2:A20, the standard formula is =ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""))). If you want to see the distinct values rather than just count them, enter =UNIQUE(FILTER(A2:A20,A2:A20<>"")) in an empty cell. Excel displays the results in the cells below; make sure that spill area is clear.

If the range might be entirely blank, FILTER has no results to return and can produce a #CALC! error. To show zero in that situation, with the same A2:A20 layout, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""))),0). IFERROR also hides other errors coming from the range or calculation, so investigate the source data if you expected a nonzero answer.

COUNTA is another way to count the array returned by UNIQUE. With the values in A2:A20 and blanks excluded, use =COUNTA(UNIQUE(FILTER(A2:A20,A2:A20<>""))). ROWS makes the one-column assumption more visible. If you are counting unique results from a two-dimensional range, decide whether uniqueness means distinct rows or distinct entries before adapting the formula.

Count values that occur exactly once

To count entries that appear only once, set UNIQUE’s third argument to TRUE. With the list in A2:A20, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""),FALSE,TRUE)),0). In this formula, FALSE means examine the range by column, while TRUE requests items that occur exactly once. The FILTER step excludes blanks, and IFERROR returns zero if no nonblank items qualify.

For the example Red, Blue, Red, Green, this formula returns 2, not 3. It does not count every row whose value is different from the one above it; it checks how often each value occurs throughout the filtered range. That makes it useful for spotting one-time codes, names, or products, but it is not a substitute for checking whether a record is valid.

Keep the two results separate in reports: “distinct products” and “products sold exactly once” answer different questions. A frequent source of confusion is interpreting the UNIQUE function’s exactly-once option as a request to remove duplicates and then count all remaining distinct values. TRUE specifically discards values that had duplicates.

Count unique values that meet a condition

You can combine FILTER and UNIQUE to count distinct values within a category. Suppose department names are in A2:A100, employee names are in B2:B100, and the department to analyze is entered in E2. With that layout, use =IFERROR(ROWS(UNIQUE(FILTER(B2:B100,(A2:A100=E2)*(B2:B100<>"")))),0). It counts nonblank employee names in the selected department, with each name counted once.

The multiplication in the FILTER condition acts like AND: a row must match the department and have a nonblank employee name. Change the department in E2 to reuse the result. For more conditions, multiply additional true-or-false tests into the include argument, taking care that each condition range has the same number of rows as the values being filtered.

For instance, if you need unique names for one department and one status, add a test for the status range and its selected status cell in the same way. If there are no matching rows, FILTER returns no results; the IFERROR wrapper in the example displays zero. As with other IFERROR formulas, it can conceal an unrelated error, so remove the wrapper temporarily when troubleshooting.

Excel versions and formula behavior

UNIQUE, FILTER, and the dynamic-array behavior used above are available in Microsoft 365 and Excel 2021 or later. Excel 2019 and earlier do not support these functions. If a formula returns #NAME?, check the Excel version and make sure function names and separators match the installation’s language and regional settings. The examples here use English function names and comma separators.

By default, UNIQUE treats text comparisons without regard to capitalization: “Maple” and “MAPLE” are generally treated as the same value for this purpose. If capitalization distinguishes records in your system, the basic formula is not a case-sensitive count. You would need a separate case-sensitive approach, such as one based on EXACT, and should test it against representative data.

Numbers stored as numbers and text that looks like a number may also be different entries. For example, a numeric 12 and the text value “12” can be counted separately. If the count seems unexpectedly high, check for numbers stored as text, leading or trailing spaces, inconsistent punctuation, and other variations. Clean or standardize the source data when those variations should represent the same item.

Alternatives for older Excel and repeatable reports

If you do not have UNIQUE and FILTER, a PivotTable can provide a distinct count in supported desktop versions of Excel. On Windows, select the data and choose Insert > PivotTable. In the Create PivotTable dialog, select “Add this data to the Data Model,” then create the PivotTable. Put the field you want to count in the Values area, open its Value Field Settings, and choose Distinct Count. The option depends on adding the data to the Data Model; it is not the same as the ordinary Count summary.

PivotTables are useful when you want to group distinct counts by department, month, or another field and refresh the summary as data changes. They are less convenient if you need a result directly inside a formula-driven report. The Data Model option and available PivotTable features can differ by Excel edition and platform, so check the dialog in your version; the steps above describe desktop Excel for Windows.

Power Query is another option for a repeatable cleanup workflow. Convert the source range to a table, then use Data > From Table/Range to open the Power Query Editor. Select the column or columns that define a duplicate, choose Home > Remove Rows > Remove Duplicates, and then load the result back to Excel. Count the remaining records, or use the deduplicated table as the source for a report. Refresh the query when the source changes. Removing duplicates changes the query output, not the original table, unless you choose to replace or overwrite data yourself.

Older Excel versions also have formula techniques based on COUNTIF and SUMPRODUCT, but they are more sensitive to range shape and special characters such as wildcard symbols. They can be a practical compatibility option for a simple, controlled list, but PivotTables or Power Query may be easier to audit. Avoid copying a legacy formula into a different layout without understanding how it handles blanks, errors, and text.

Common counting problems to check

  • Blank cells: Decide whether blank means “not a record” or a legitimate category. Filtering out empty strings also removes cells whose formulas return an empty string.
  • Extra spaces: “North” and “North ” can be different text values. TRIM can remove ordinary extra spaces, but imported text may contain nonbreaking spaces that require additional cleanup.
  • Errors in the source: An error value can flow through FILTER or UNIQUE. Find and correct the error, or deliberately handle it before counting rather than hiding every problem with IFERROR.
  • Wrong range: Confirm the start and end rows include all records and exclude headers. If new records are added often, format the data as an Excel table and use a table column in your formula so the source expands with the table.
  • Different meanings of a record: Counting unique order numbers is not the same as counting unique customers. Choose the column, or combination of columns, that represents the entity in your question.

When checking a result, display the distinct list with UNIQUE and compare it with the source. This is a quick way to spot misspellings or formatting variants that inflate a count. For a larger operational report, keep the source data unchanged and put any normalization in a helper column or a clearly documented Power Query step.

Frequently Asked Questions

How do I count unique values in a column without counting blanks?
With the data in A2:A20, enter =ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""))) in Microsoft 365 or Excel 2021 and later. It counts distinct nonblank entries. If the range could be all blank, wrap it in IFERROR to show zero.

How do I count values that appear only once?
Use UNIQUE’s exactly-once option. With data in A2:A20, =IFERROR(ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""),FALSE,TRUE)),0) counts nonblank values whose frequency in that range is one. This is a different result from counting all distinct values.

Can I count unique values based on another column?
Yes. Filter the value column using a condition on the category column, then apply UNIQUE and ROWS. For example, the department-and-employee formula above counts distinct employees for the department selected in E2.

Why does my unique count seem too high?
Look for spaces, spelling differences, numbers stored as text, and values that differ only in formatting. Excel’s basic UNIQUE comparison is not case-sensitive, but otherwise distinct text forms can remain separate. Showing the UNIQUE result list often reveals the cause.

Can I count unique values in Excel 2019 or earlier?
Those versions do not include UNIQUE and FILTER. Use a PivotTable with “Add this data to the Data Model” and the Distinct Count summary when available, or use Power Query or a carefully chosen legacy formula method.

Does UNIQUE count duplicate values once or remove them entirely?
By default, UNIQUE returns one instance of each distinct value, so counting its results gives a distinct count. Its exactly-once option instead returns only values that occurred once in the source.

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.