To count distinct, nonblank entries in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range. The formula returns the number of different values, ignoring blank cells; for example, a list containing Ana, Ben, Ana, and Chen has 3 unique entries.
The quickest formula for a unique count
In Excel for Microsoft 365 or Excel 2021, enter the formula in an empty cell and press Enter. It filters out blanks, creates a list of distinct values, and counts the rows in that list. IFERROR makes the result 0 if the selected range contains no nonblank entries, instead of showing an error.
For a concrete example, suppose A2:A9 contains Ana, Ben, Ana, a blank cell, Chen, Ben, Dana, and another blank cell. With those names in A2:A9, this formula returns 4:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A9,A2:A9<>""))),0)
UNIQUE and FILTER are available in Microsoft 365 and Excel 2021. In Excel for the web, these functions are also available, though a workbook’s interface and feature availability can vary. The formula works directly with a normal range; converting the data to an Excel Table is optional, but can make a range easier to maintain as rows are added.
What Excel means by a unique entry
A unique count is the number of different values in a range, with each value counted once. It is sometimes called a distinct count. It is not the same as counting every nonblank cell: if a product appears 15 times, a nonblank count includes all 15 records, while a unique count includes that product once.
It also helps to distinguish a count from a list. The formula above returns a single number. The UNIQUE function by itself returns the distinct values as a spilled list in nearby cells. That list can be useful for checking which items Excel counted, while the row-count formula is useful for a dashboard or summary figure.
- Count entries: counts occupied cells, including repeated values.
- Count unique entries: counts different values, with repetitions counted once.
- List unique entries: displays one instance of each value rather than returning a count.
These distinctions matter when reviewing customer names, order IDs, inventory items, survey answers, or any other data where repeated records are expected. Decide first whether a blank should be ignored and whether “different” means different text, different numbers, or different combinations of fields.
How the formula works
The formula combines three functions, each with a specific job. FILTER keeps only values that meet a condition. UNIQUE removes repeated values from the filtered results. ROWS counts how many entries remain. IFERROR handles the special case where filtering finds no qualifying cells.
- FILTER(A2:A100,A2:A100<>””) returns values from A2:A100 only when the corresponding cell is not blank.
- UNIQUE(…) returns one instance of each value in that filtered result.
- ROWS(…) counts the items in the resulting vertical list.
- IFERROR(…,0) returns 0 if the inner calculation produces an error because there are no qualifying entries.
With names in A2:A9, the formula =SORT(UNIQUE(FILTER(A2:A9,A2:A9<>""))) spills a sorted list of the distinct, nonblank names. In the Ana, Ben, Ana, blank, Chen, Ben, Dana, blank example, the resulting list contains Ana, Ben, Chen, and Dana. SORT is optional for counting, but helpful if you want to inspect or present the unique values in order.
Handling blanks, spaces, and empty-looking cells
The condition A2:A100<>"" excludes truly empty cells and cells whose formulas return an empty string. This is often the expected behavior for a list with incomplete rows. A blank cell does not represent a meaningful category in many reports, so excluding it prevents it from adding to the distinct count.
A cell containing one or more spaces is not actually blank. Excel treats a space as text, so it can appear as a distinct entry even though it looks empty. If your source data may contain accidental spaces, clean it before counting. For example, use TRIM in a helper column to remove extra spaces around ordinary text, then count the cleaned values. TRIM does not remove every possible nonprinting character, so imported data may need additional cleanup.
Be aware that a formula returning an empty string can look blank but is not an empty cell. The FILTER condition used above excludes it because its displayed result equals an empty string. If you deliberately want a blank to count as one distinct entry, remove the blank-exclusion condition and choose a formula that suits that policy; test it against a small sample first, since counting empty cells can be confusing when the selected range extends well beyond the data.
Counting unique entries within a category
A common follow-up is counting distinct items for one group, such as different products sold in one region. Suppose regions are in A2:A9, product names are in B2:B9, and the region to summarize is in E2. With those cell layouts, use:
=IFERROR(ROWS(UNIQUE(FILTER(B2:B9,(A2:A9=E2)*(B2:B9<>"")))),0)
This returns the number of different nonblank products whose region matches E2. For example, if E2 contains West and the matching product records are Bolt, Nut, Bolt, and Washer, the result is 3. The multiplication in the FILTER condition means both tests must be true: the region must match E2 and the product must not be blank.
For a summary covering many regions, put each region name in a summary column and copy the formula down, adjusting E2 to the relevant row as needed. Excel adjusts ordinary cell references when you copy a formula. If your data changes often, consider converting it to a Table and using structured references so formulas can include new rows more naturally. Check the result when region or product names have inconsistent spelling, since West, west, and West with a trailing space can cause misleading results.
Using a PivotTable for a distinct count
A PivotTable can be a useful choice when you need a distinct count alongside other summaries, such as total sales by region and the number of different customers in each region. In Excel for Microsoft 365 on Windows, select the data, choose Insert > PivotTable, and select the option to add the data to the Data Model in the PivotTable creation dialog. Add the grouping field to Rows and the field to count to Values. Then open Value Field Settings for that value field and choose Distinct Count.
The Data Model requirement is important: the Distinct Count calculation is not available in every ordinary PivotTable. Availability and interface details differ in Excel for Mac and Excel for the web; if the Data Model option or Distinct Count setting is not present, use the formula method or check the options in your installed edition. A PivotTable is especially practical for interactive summaries, but it takes more setup than a one-cell formula and may need refreshing after source data changes.
Do not confuse a PivotTable’s distinct count with Remove Duplicates. Remove Duplicates changes the selected data by deleting repeated rows, while a PivotTable summarizes records without deleting the source list. If you only want to inspect a clean list, copy the original data first or use UNIQUE so the source remains intact.
Options for older Excel versions
Excel 2019 and earlier do not include the modern UNIQUE and FILTER functions. If you use one of those versions, a PivotTable with a Data Model distinct count may be available in your Windows desktop edition. Another practical option is to copy the column to a separate area, use Data > Remove Duplicates on that copy, and count the remaining entries. Because Remove Duplicates edits cells, never apply it to the only copy of important source data.
Older Excel versions also support more complex array formulas for distinct counts, but their behavior can depend on the data types, blanks, errors, and the way the formula is entered. Some require Ctrl+Shift+Enter rather than Enter. A formula copied from a different Excel version may therefore give an incorrect result or fail to calculate. For a durable workbook, document the version and assumptions, and test the result against a short list where you can verify the distinct values by eye.
Common problems and checks
If the result seems wrong, the issue is often in the source data rather than in the counting function. A useful check is to display the unique list separately and compare it with the original entries. This can reveal hidden spaces, spelling variations, numbers stored as text, or values that appear identical but are not identical internally.
- Formula shows #NAME?: Your Excel version may not support one of the functions, or the function name may be misspelled.
- Formula shows #CALC!: FILTER may have found no matching values. The IFERROR version returns 0 in that case.
- Unexpected extra entry: Look for spaces, nonprinting characters, or inconsistent spelling in the source.
- Two similar values count separately: Check whether one is stored as text and the other as a number, or whether punctuation differs.
- Count does not update as expected: Check that the formula’s range includes new rows. A fixed range such as A2:A100 will not include row 101.
Excel’s ordinary text comparisons are generally not case-sensitive for this kind of duplicate check, so entries such as Ana and ANA are normally treated as the same value. If capitalization must define a different entry, the standard UNIQUE formula is not enough; use a deliberate case-sensitive method and verify it on representative examples. Also remember that a number formatted to display with leading zeros may have the same underlying value as the unpadded number. Cell formatting alone does not necessarily create a distinct value.
Frequently Asked Questions
How do I count unique values in Excel while ignoring blanks?
In Microsoft 365 or Excel 2021, with the values in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). It excludes empty cells and empty-string results, then counts the remaining distinct values.
How do I get a list of unique values instead of a number?
With values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. Excel spills the distinct entries into the cells below. To omit blanks and sort the list, use =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))).
Can I count unique values for each department or region?
Yes. Filter the value range using a condition for the chosen department or region, then pass the result to UNIQUE and ROWS. The category example above shows the formula for regions in column A, items in column B, and the selected region in E2.
Does UNIQUE count blank cells?
It can return a blank as an entry when blanks are present in the source range. To avoid treating blanks as a category, filter them out with a condition such as A2:A100<>”” before applying UNIQUE.
Can I count unique values in Excel 2019?
Excel 2019 does not include the UNIQUE and FILTER functions. Depending on your edition, use a PivotTable distinct count with the Data Model, or remove duplicates from a copy of the data and count the remaining entries. Confirm the available PivotTable options in your version.
Related
- Convert Text to Numbers in Excel: Methods That Work
- Convert Excel to ZIP: How to Compress and Share a Workbook
- Convert an Excel Binary Worksheet (.XLSB) to Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average Days Between Dates in Excel: Formulas & TipsIf your dates are listed in chronological order in A2:A10, enter =AVERAGE(A3:A10-A2:A9) to calculate the average number of…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- How to Sort Numbers in Excel in Numerical OrderTo sort numbers in Excel, click a cell in the number column, then choose Data > Sort Smallest…
- Abby Lawson Budget Spreadsheet: Find the Right FileIf you’re looking for an Abby Lawson budget spreadsheet, the name alone does not identify a confirmed official…
- HLOOKUP in Excel: How Horizontal Lookups WorkHLOOKUP in Excel searches across the first row of a range for a value, then returns a value…
- Excel Percent Change: Formula, Examples, and TipsTo calculate percent change in Excel, subtract the old value from the new value, then divide by the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.