To count distinct, nonblank values in an Excel list, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your range. In Microsoft 365 for Windows, this counts each different value once, ignores empty cells, and returns 0 if the range has no nonblank entries.
What “unique” means in an Excel count
In everyday spreadsheet questions, “count unique values” usually means count the distinct entries: repeated values contribute only once. If a list contains Ana, Ben, Ana, and Carla, the distinct count is 3, even though there are 4 filled cells. Excel’s UNIQUE function returns one instance of each distinct value; the formula above counts the rows in that result.
There is a related distinction worth knowing. In Excel’s terminology, values that appear exactly once are unique, while distinct values may appear one or more times. For the example above, only Ben and Carla appear exactly once, but there are three distinct names. If you want a count of entries that occur only once, use a different calculation rather than the basic distinct-count formula.
Decide how to treat blanks before choosing a method. Most lists use blank cells to mean “no data,” so the main formula excludes them. If a blank is meaningful as its own category, make that category explicit—for example, replace missing entries with a label such as “Not provided”—so it is not confused with an accidental empty cell.
How the Microsoft 365 formula works
The formula combines three functions. FILTER keeps only cells that are not empty, UNIQUE returns one copy of each distinct entry, and ROWS counts how many entries remain. IFERROR handles a list with no qualifying values: when FILTER has nothing to return, the formula displays 0 instead of an error.
For example, suppose A2:A10 contains Ana, Ben, Ana, a blank cell, Carla, Ben, Dev, Carla, and another blank. With names in A2:A10, the formula returns 4: Ana, Ben, Carla, and Dev are the four distinct nonblank entries. Repeated names do not increase the count, and the two blank cells are excluded.
UNIQUE, FILTER, and the dynamic-array behavior used here are available in Microsoft 365 and Excel 2021 or later. The result is a single number, so it does not spill a visible list into neighboring cells. If you also want to inspect which values were counted, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell; Excel spills the distinct nonblank entries below it. Allow room for the spilled results and make sure the cells beneath the formula are empty.
Steps for counting a range or an Excel table
For a fixed range, enter the formula in a cell outside the source list. For a reusable list, an Excel Table is often more convenient because its structured reference expands as rows are added.
- Select a cell in the list and choose Insert > Table. Confirm that the data has headers if it does, and select OK.
- Give the table a recognizable name on the Table Design tab, and note the heading of the column you want to count.
- In a cell outside the table, enter the formula using the table and column names. For example, with a table named Sales and a column named Customer, enter
=IFERROR(ROWS(UNIQUE(FILTER(Sales[Customer],Sales[Customer]<>""))),0).
The table reference adjusts as records are added or removed, so the count updates with the data. Keep the formula cell outside the table’s data column to avoid mixing the result into the list being counted. If you rename a table or heading, Excel normally updates structured references in formulas; if you type a reference manually, match the displayed names exactly.
Counting values that appear exactly once
Sometimes the actual question is “How many entries occur only once?” rather than “How many different entries are in the list?” With the same example, Ana and Ben repeat, while Carla and Dev occur once, so the answer is 2. This is a frequency question: first identify each distinct value, then check how often it occurs.
With values in A2:A100, this Microsoft 365 formula counts the nonblank values whose frequency is one: =IFERROR(LET(r,FILTER(A2:A100,A2:A100<>""),u,UNIQUE(r),SUM(--(COUNTIF(r,u)=1))),0). It filters out blanks, creates a distinct list, checks each item’s count, and sums the TRUE results. LET is available in Microsoft 365 and Excel 2021 or later. If you only need the number of distinct categories, use the simpler formula in the opening paragraph instead.
Options in older Excel versions
Excel 2019 and earlier do not include the worksheet functions UNIQUE and FILTER. A commonly used formula for a vertical range of ordinary text or numbers is =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). With the names in A2:A10 from the earlier example, it returns 4 and ignores blank cells.
This older formula counts each nonblank entry as a fraction of its frequency: if Ana appears twice, each occurrence contributes one-half, for a total contribution of 1. SUMPRODUCT adds those contributions. It is less readable than the modern formula and has limitations, especially with error values or entries containing wildcard characters such as an asterisk or question mark. Check the result against a small sample before relying on it for complicated data. In older Excel, some formula techniques may also require array entry; this SUMPRODUCT pattern is normally entered with Enter, not Ctrl+Shift+Enter.
Another choice is a PivotTable when you need a summarized report rather than a formula cell. Create a PivotTable with Insert > PivotTable, select the option to add the data to the Data Model, and place the field in Values. Open Value Field Settings and choose Distinct Count under Summarize Values By. Distinct Count is available for a PivotTable using the Data Model; it is not an option for every ordinary PivotTable. The PivotTable is useful for reports, but refresh it after the source data changes.
Formula, PivotTable, or Power Query?
Choose based on whether you need a live number, a report, or a cleaned dataset. A dynamic-array formula is usually the shortest route to a count that recalculates as worksheet values change. A Data Model PivotTable works well when the distinct count belongs alongside totals, categories, or other summarized fields. Power Query is useful when removing duplicates is part of a repeatable import-and-clean process.
- Formula: Best for a count displayed directly on a worksheet. It updates with recalculation and is easy to place beside the source data.
- PivotTable: Best for interactive summaries and comparisons. It requires the Data Model for the Distinct Count setting and generally needs a refresh after source changes.
- Power Query: Best for repeatable transformations, especially when data is imported regularly. Select the column in the query editor, choose Home > Remove Rows > Remove Duplicates, then use Home > Close & Load. The resulting table contains one row per distinct value; count its rows if a standalone count is required. Refresh the query to process updated source data.
These methods do not all behave identically in every workflow. A formula recalculates against worksheet cells, while a PivotTable or query may show an earlier result until refreshed. If a result looks stale, check whether the source changed and whether the PivotTable or query needs refreshing.
Common causes of unexpected counts
Extra spaces are a frequent source of apparent duplicates. “Miller” and “Miller ” can be treated as different text because the second entry contains a trailing space. If the source is inconsistent, clean it in a helper column before counting. With text in A2, a basic cleanup formula is =TRIM(A2); fill it down, then count the cleaned column. TRIM removes ordinary extra spaces but does not remove every possible nonprinting character, such as some characters copied from web pages.
Also check whether values that look alike are stored differently. A number such as 25 and text containing “25” may not be equivalent for every comparison or downstream task. Dates can also be stored as text instead of true Excel date values. Standardize the source type when possible, and inspect a few entries if the count seems unexpectedly high.
Excel’s usual text comparisons are not case-sensitive for this kind of distinct counting, so entries such as “north” and “North” are generally treated as the same category. If capitalization is meaningful in your data, do not assume the basic formula provides a case-sensitive count; use a specifically designed case-sensitive method. Finally, errors in the source range can interfere with a formula, so correct or deliberately exclude them rather than interpreting an error as a valid category.
Frequently Asked Questions
How do I count unique values in Excel while ignoring blanks?
With a vertical list in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). It excludes empty cells, counts each distinct remaining value once, and returns 0 when there are no nonblank values.
What is the difference between unique and distinct values?
A distinct value is one different category in the list, even if it appears several times. A value that is unique in the strict sense appears exactly once. “Count unique” is often used informally to mean a distinct count, so confirm which meaning the question requires.
Can I count distinct values in Excel without UNIQUE?
Yes. In older Excel, a SUMPRODUCT and COUNTIF formula can count ordinary text or numbers while excluding blanks. A PivotTable with the data added to the Data Model can also use Distinct Count. The formula is convenient for a worksheet result; the PivotTable is better suited to a summary report.
Why is my unique count higher than expected?
Look for leading or trailing spaces, nonprinting characters, inconsistent number-versus-text entries, or values that only appear identical on screen. Also decide whether blanks and errors should be included. Cleaning and standardizing the source often resolves a surprising count.
Does the formula update when I add more rows?
A fixed reference such as A2:A100 covers only that range. An Excel Table reference, such as Sales[Customer], expands when new table rows are added and is usually the better choice for a growing list. PivotTables and Power Query outputs may need a refresh instead.
Choosing the best approach
For most current Excel users, start with the FILTER and UNIQUE formula: it is compact, ignores blanks deliberately, and produces a count that responds to changes in the list. Use a Table reference if the list will grow. If you are working in an older version, use a compatible formula or a PivotTable with the Data Model, and be explicit about whether “unique” means distinct values or values occurring only once. Cleaning the source and deciding how blanks, case, and inconsistent data should be handled will make any method more dependable.
Related
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
- Copy Paste in Excel: Keep Column Widths When Pasting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Wedding Budget Spreadsheet for 50 Guests: Costs & FormulasA practical wedding budget spreadsheet for 50 guests should list each cost, its planned amount, the latest expected…
- How to Filter Highlighted Cells in Excel by Cell ColorTo filter highlighted cells in Excel, turn on filtering, open the drop-down arrow in the column that contains…
- JavaScript Functions: How They Work and When to Use ThemIn JavaScript, a function is a reusable block of code that can receive values, perform work, and optionally…
- Excel Won’t Calculate Formulas? Causes, Checks, and FixesIf Excel does not calculate a formula, first check Formulas > Calculation Options and select Automatic. If that…
- Excel TEXTJOIN Limit: Character Cap and WorkaroundsIn Excel, TEXTJOIN can return a maximum of 32,767 characters in one cell—the worksheet cell-content limit. If its…
- How to Split Cell Data in Excel: Text to Columns & FormulasTo split cell data in Excel, select the cells, choose Data > Text to Columns, pick Delimited or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.