To count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range. The formula filters out blank cells, returns each remaining value once, and counts the results. If you have an older Excel version, use a PivotTable or the legacy formula below.
What You’ll Need
- An Excel workbook with the values you want to count in one column, such as product names in A2:A100. The examples assume the heading is in row 1 and the data starts in row 2.
- Excel for Microsoft 365 or Excel 2021 to use UNIQUE and FILTER. These functions also work in Excel 2024. For earlier versions, use the PivotTable method or the older formula in Step 5.
- A clear rule for what counts as the same value. Excel’s normal text comparisons are not case-sensitive, so “Pear” and “pear” count as one value. Extra spaces, however, can make otherwise similar entries count separately.
Step-by-Step
- Choose the data to count. Identify the column containing the values and note its first and last data cells. For example, if the heading is in A1 and the list runs from A2 through A100, use A2:A100. Avoid selecting the heading, since it would be counted as a value. If your list will grow, you can use an Excel Table so the formula can expand with new rows; select the data and choose Insert > Table, then confirm whether your data has headers.
- Decide how blank cells should be handled. In most lists, blanks are not meaningful values and should not count. The main formula below excludes blanks. It also returns 0 if the selected range contains no nonblank values. If you intentionally want a blank to count as one unique item, use a formula without FILTER, such as
=IFERROR(ROWS(UNIQUE(A2:A100)),0). With values in A2:A100, this counts every distinct result from UNIQUE, including a blank result when blanks are present. - Enter the formula in an empty cell. With the source list in A2:A100, click a result cell outside the list, such as C2, and enter
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). Press Enter. In current Excel, the functions return a dynamic array behind the scenes, but ROWS counts that array and displays one total in C2. The IFERROR wrapper makes an empty or all-blank range return 0 instead of an error. - Check the result against a small example. Suppose A2:A10 contains Apple, pear, Apple, a blank cell, Pear, orange, pear, banana, and banana. With the formula pointed at A2:A10, the answer is 4: Apple, pear, orange, and banana. The repeated entries are counted once, the blank is excluded, and Pear and pear are treated as the same text value. This quick check helps confirm that the formula refers to the intended rows.
- Adjust the range when your list changes. If you add data through row 150, update the reference in the formula from A2:A100 to A2:A150. To make the result easier to maintain, convert the source list to a Table and use its column name in the formula. For example, if the table is named Sales and the column is Item, the formula can be
=IFERROR(ROWS(UNIQUE(FILTER(Sales[Item],Sales[Item]<>""))),0). This counts nonblank items in the table column and automatically includes rows added to that table. - Use a PivotTable when you want a report as well as a count. Select your source data, choose Insert > PivotTable, and in the dialog select Add this data to the Data Model before creating the PivotTable. Drag the field you want to count into Values. Open its dropdown, choose Value Field Settings, select Distinct Count, and choose OK. The resulting value counts each different item once. The Data Model option and Distinct Count are available in Excel for Microsoft 365 on Windows; Excel for Mac and Excel for the web may not offer this same workflow, so use the formula there if the option is absent.
- For older Excel, try a legacy formula. In Excel 2019 or earlier, where UNIQUE and FILTER are unavailable, with values in A2:A100 enter
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). It counts nonblank entries once each without requiring a special key combination. On the example list above, it returns 4. This approach is convenient for ordinary text and numbers, but COUNTIF treats asterisks and question marks as wildcard characters; see Troubleshooting if those characters occur as literal data.
Common Mistakes
Counting the heading. If you include A1 in the range, the heading itself becomes another distinct value. Start with the first data row, not the column label. Similarly, check that the range ends at the final row you intend to include; a too-short range silently misses later entries.
Assuming blanks are counted as ordinary values. The recommended formula deliberately filters out blanks. This is useful for most lists, but it may not match a task where an empty entry is meaningful. Decide the rule first and use the version with or without FILTER accordingly. A formula returning 0 can be correct when every cell is blank.
Overlooking spaces or inconsistent entry formats. “Orange” and “Orange ” can be treated as different entries because the latter has a trailing space. Numbers stored as text can also behave differently from numeric values in some workflows. Clean the source data or use a helper column with TRIM when appropriate, and verify the cleaned values before relying on the count.
Expecting case-sensitive results. UNIQUE is intended for ordinary distinct-value comparisons, not for distinguishing text solely by capitalization. If “ABC” and “abc” must count separately, a different case-sensitive approach is needed; do not assume the standard formula will separate them.
Using a dynamic-array formula in an unsupported version. If Excel displays #NAME? for UNIQUE or FILTER, the installed version may not include those functions. Use a PivotTable with Distinct Count where available, or the legacy SUMPRODUCT formula for compatible data.
Tips
Keep the result outside the source range. That prevents the formula from accidentally including its own output and makes it easier to spot the answer. If the result is part of a dashboard, label the cell clearly, such as “Unique items,” and format it as a number rather than as text.
For repeatable work, convert the data to a Table with Insert > Table. Structured references such as Sales[Item] are usually easier to read than a large range of row numbers, and the Table grows as new rows are entered. Make sure the formula uses the actual Table and column names shown in your workbook; the names in the example are illustrative.
If you need to count unique combinations across two columns, UNIQUE can work with a multi-column range. With a person’s first and last names in A2:B100 and blank rows excluded when both cells are empty, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:B100,(A2:A100<>"")+(B2:B100<>"")))),0). This counts distinct first-name/last-name pairs, not distinct people with identical names. The plus sign combines the two nonblank tests as an OR condition, so a row is retained if either cell contains something.
For a quick visual audit, place =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell when your source values are in A2:A100. Excel spills the distinct nonblank values into nearby cells. Compare that list with the total from the ROWS formula. Leave enough empty space below the formula cell so the spilled results are not blocked.
On Windows, Enter confirms a formula. On Mac, Enter or Return confirms it depending on keyboard settings. The formulas here use dynamic arrays and do not need Ctrl+Shift+Enter in supported versions. If you are editing an older array formula from another source, follow the instructions for that particular formula and Excel version.
Troubleshooting
The formula shows #NAME? Check whether Excel recognizes UNIQUE and FILTER. They require Excel 2021, Excel 2024, or Microsoft 365. In older releases, use the legacy formula or a PivotTable if Distinct Count is available. Also check for misspelled function names and make sure you are entering the formula in Excel rather than a program that does not support these functions.
The answer is larger than expected. Look for spaces before or after entries, spelling variations, and values that are genuinely different, such as “Item 7” and “Item 07.” To remove ordinary leading and trailing spaces, you can create a cleaned helper column. With original text in A2, enter =TRIM(A2) in B2 and fill it down, then count the cleaned values in column B. TRIM removes repeated internal spaces as well as leading and trailing spaces, so inspect the result if internal spacing is significant.
The answer is smaller than expected. The formula treats text entries that differ only by capitalization as the same. It can also miss data outside the referenced range. Check the formula bar to confirm the start and end cells, then review whether the source values are actually different under Excel’s usual comparison rules.
You see #SPILL! after entering UNIQUE on its own. A spill formula needs clear cells for its results. Delete or move any content blocking the cells beneath or beside the formula. If you only need a total, use the ROWS-based formula instead; it returns one number and does not need to display the list.
The older formula gives an unexpected count with symbols. COUNTIF interprets * and ? as wildcards in criteria, so the legacy formula can miscount values containing those characters. For such data, use the dynamic-array formula in a supported version or a PivotTable Distinct Count. Also check that the source range does not contain errors, which can affect calculations.
Frequently Asked Questions
What is the difference between a unique count and a total count?
A total count measures entries, often including repeats. A unique count measures different values only. If a list contains five rows but just three different products, its total row count is five and its unique product count is three.
Does the formula count blank cells?
The recommended formula filters out blanks, so an empty cell does not add to the result. If the selected range has no nonblank values, IFERROR returns 0. Remove the FILTER portion if you need blanks included as a distinct result.
Can I count unique numbers as well as text?
Yes. UNIQUE and FILTER can count distinct numeric values in the same way. If numbers are stored as text in some rows and as numbers in others, standardize the column first if you want those entries treated consistently.
Can I count distinct values using multiple criteria?
Yes, but the filter condition must express which rows qualify. For example, you could filter a product column using a matching department column and then count the resulting unique products. Make sure both ranges start and end on the same rows so each condition lines up with the correct record.
Why does my PivotTable not show Distinct Count?
When creating the PivotTable on supported Windows desktop Excel, select Add this data to the Data Model in the creation dialog. Distinct Count is a Data Model summary option and may not appear if the PivotTable was created without that selection. If your Excel edition does not provide the option, use the formula method instead.
Can I make the unique count update when new rows are added?
Yes. Convert the source range to an Excel Table and use a structured reference such as Sales[Item] in the formula. When you add a row to the Table, the reference expands, so the count includes the new entry without manually changing the last row number.
Related
- Adding a Checkbox to Excel: Setup, Linking, and Tips
- Average Growth Rate in Excel: Calculate It Step by Step
- How to Calculate an Average in an Excel Column
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Between Two Sheets: Examples and Common FixesTo use VLOOKUP across two Excel sheets, put the lookup value on one sheet and the data table…
- Round Excel Cell Values: Formulas and Display OptionsTo round a number in an Excel cell and change the value used in calculations, use the ROUND…
- Excel Text Functions List: Names, Uses, and ExamplesExcel’s text functions help you extract, combine, clean, compare, format, and convert text or values. The main functions…
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
- Microsoft Excel SUMIF Examples and How to Use ThemIn Microsoft Excel, SUMIF adds values that meet one condition. For example, with categories in A2:A6 and amounts…
- 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.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.