In Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) to count distinct nonblank values in A2:A100. It returns 0 if the range contains no nonblank values; to count a simple range without filtering blanks, use =COUNTA(UNIQUE(A2:A100)), keeping in mind that blank cells can affect the result.
The recommended formula and how to use it
Suppose a list of product names is in A2:A100 and you want the number of different names, not the number of filled cells. Enter the first formula in an empty cell outside the source data. It filters out blank entries, creates a list of distinct remaining values, counts the rows in that list, and returns zero if there is nothing to count.
For example, if A2:A7 contains Oak, Pine, Oak, Maple, a blank cell, and Pine, the result is 3: Oak, Pine, and Maple are the three distinct nonblank entries. The formula does not change or rearrange the source list. You can place it in a summary area or on another worksheet.
UNIQUE, FILTER, and ROWS work together here. UNIQUE produces distinct entries; FILTER removes values that meet the blank test; and ROWS counts the entries in the resulting vertical list. IFERROR handles the case where filtering finds no values, which would otherwise produce an error.
What Excel means by a unique value
A unique count is a count of distinct values. It is not the same as counting every entry, and it is not necessarily a count of values that appear only once. If Oak occurs five times, Oak still contributes one to a distinct count. A value that occurs exactly once is a different question: that asks for entries whose frequency is one.
For a basic example, a list containing 12, 12, 18, and 24 has three distinct values, even though it has four entries. A list containing 12, 12, and 18 has two distinct values, but only 18 appears exactly once. This distinction matters when interpreting reports, inventory lists, customer counts, or other summaries.
Excel’s UNIQUE function is available in Microsoft 365 and Excel 2021 or later. It returns a dynamic array, so the distinct list can spill into nearby cells when you enter the function on its own. The recommended formula counts that array with ROWS instead of displaying the list. In Excel for the web, the formula functions are available as well; the same formula syntax generally applies.
Handling blank cells and empty-looking values
Blank handling is one of the main reasons to choose the filtered formula rather than simply counting the result of UNIQUE. A source range often extends below the data, or contains gaps between entries. If those blank cells are treated as a distinct item, the simple count can be one higher than expected. Filtering for nonblank cells makes the intended rule explicit.
The test A2:A100<>"" excludes genuinely empty cells and cells whose formulas return an empty string. It does not remove cells containing spaces. A cell that looks empty but contains one or more spaces is nonblank to Excel, so it may count as a distinct value. If pasted data has stray spaces, clean the source or use a helper column to standardize it before counting.
There is a related distinction between a truly empty cell and a formula that displays nothing. Both are usually unwanted in a nonblank count, and the test above excludes them. If you want blanks to count as one distinct category, do not filter them out; decide first how the source range represents empty entries and check the result against a small sample.
Counting unique values that meet a condition
You can combine a distinct count with criteria by putting the conditions inside FILTER. Assume product names are in A2:A100 and regions are in B2:B100. To count the nonblank product names for the East region, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(B2:B100="East")))),0). The result is the number of different product names with a matching East entry.
In this formula, the multiplication sign between the tests means both conditions must be true: the product name must be nonblank and the region must equal East. Change the text East to another region, or refer to a cell containing the selected region. The ranges used for the names and regions need to cover corresponding rows; otherwise, the results may not represent the intended records.
For a condition based on a date or a numeric threshold, use the same pattern and replace the second test with the relevant comparison. Keep the distinct-count range separate from the criteria range: the former is what you want to count, while the latter determines which rows are included.
Counting distinct combinations across columns
Sometimes a “unique value” is actually a unique combination, such as a customer-and-product pair. With customer names in A2:A100 and products in B2:B100, UNIQUE can return distinct rows from both columns. To count combinations where both fields are filled, use =IFERROR(ROWS(UNIQUE(FILTER(A2:B100,(A2:A100<>"")*(B2:B100<>"")))),0).
This counts distinct pairs, not distinct customers and not distinct products. If the same customer buys the same product on several rows, that pair contributes one. A customer paired with a different product contributes another. The filter in this example excludes a row if either field is blank; adjust the rule if partially completed pairs should be included.
For a unique count based on just one column, use a one-column range as in the main example. Including extra columns changes what Excel treats as a duplicate: two rows are duplicates only when their values across the included columns match.
Options for older Excel versions
Excel versions without UNIQUE and FILTER can use a SUMPRODUCT and COUNTIF formula for a single-column, case-insensitive distinct count. With values in A2:A100, enter =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). It ignores blanks and adds a fractional contribution for each occurrence so that each repeated value totals one.
This approach is useful in older desktop versions, but it is less readable and has limitations. COUNTIF interprets the asterisk and question mark as wildcard characters in criteria, so values containing those characters can produce unexpected results. The formula also does not provide the convenient spilled list that UNIQUE does. Test it on representative data, especially if entries contain wildcard characters or errors.
A PivotTable is another option when you want a report rather than a formula. Create a PivotTable using Insert > PivotTable and, where offered, select Add this data to the Data Model. Put the field to be counted in the Values area, open Value Field Settings, and choose Distinct Count under Summarize Values By. Distinct Count is available for PivotTables built using the Data Model; it is not the standard Count calculation. A PivotTable is useful for grouping and summarizing, but it may need refreshing after the source data changes.
Choosing the right method and checking results
Choose based on the question and the Excel version in use:
- For a quick count of nonblank distinct values in Microsoft 365 or Excel 2021, use the
FILTER,UNIQUE, andROWSformula. - For a distinct count with conditions, filter the target range using one or more criteria.
- For distinct combinations, pass multiple columns to
UNIQUEand count the returned rows. - For older versions, consider the
SUMPRODUCT/COUNTIFmethod or a Data Model PivotTable.
Before relying on a result, compare it with a small hand-checked sample. Confirm whether blanks should count, whether the intended unit is a single field or a combination of fields, and whether the source contains spaces or inconsistent spellings. For instance, Excel may treat “Maple” and “Maple ” as different entries because the second value has a trailing space. A count can be mathematically correct while still reflecting messy source data.
Also distinguish a live formula from a one-time cleanup. The formula updates when its referenced data changes, while a PivotTable generally requires a refresh. The Data > Remove Duplicates command is different again: it removes duplicate records from the selected data, so it is not a safe way to calculate a count unless you intend to alter a copy of the data.
Common problems and fixes
If Excel shows #NAME?, check whether your version supports the function names in the formula and confirm they are spelled correctly. UNIQUE and FILTER require Microsoft 365 or Excel 2021 and later. If your installation uses semicolons rather than commas as formula separators because of regional settings, Excel may require that local separator when you enter a formula; the examples here use comma separators.
If the answer is one too high, inspect the range for blank cells or formulas returning empty strings, and use the filtered version. If it is higher than expected for other reasons, look for leading or trailing spaces, inconsistent labels, or a range that includes unrelated cells. If it is lower than expected, verify that the formula’s range reaches the last data row and that any criteria match the underlying values.
If the formula returns zero unexpectedly, check that the source cells are in the range you referenced and that conditional criteria match exactly as intended. For date criteria, ensure the cells contain real Excel dates rather than text that only looks like a date. Errors already present in source cells can also flow into array calculations; resolve or deliberately handle those source errors if they prevent the count from evaluating.
Frequently Asked Questions
What is the simplest formula to count unique values in Excel?
For nonblank values in A2:A100 in Microsoft 365 or Excel 2021, use the filtered UNIQUE formula in the opening section. If blanks are not a concern, COUNTA(UNIQUE(A2:A100)) is shorter, but check how empty cells affect your data first.
How do I count unique values while ignoring blanks?
Use FILTER to keep only entries that are not equal to an empty string, then count the rows of the UNIQUE result. The recommended formula wraps that calculation in IFERROR so an entirely blank range returns zero.
Can I count unique values with a condition?
Yes. Put the condition into FILTER alongside the nonblank test. The East-region example above counts distinct product names only among rows whose region is East.
Does the formula count values that appear only once?
No. A distinct count counts each different value once, even if it appears many times. Counting values that occur exactly once is a separate frequency-based task and needs a different formula.
Will the formula work in Excel for Mac?
The formula functions and syntax are available in current Microsoft 365 and Excel 2021 versions for Mac as well as Windows. Function availability depends on the Excel version, and regional settings can affect whether your installation expects commas or semicolons between arguments.
Related
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Percentile Function: Rank, Syntax, and ExamplesExcel has separate functions for percentiles and percentile ranks: use PERCENTILE.INC or PERCENTILE.EXC to find the value at…
- Excel Formula for Percentage of a NumberTo calculate a percentage of a number in Excel, multiply the number by the percentage: if A2 contains…
- Excel Formula for Age at a Certain Date Using DATEDIFTo calculate someone’s age on a particular date in Excel, use =DATEDIF(A2,B2,"Y"), where A2 contains the birth date…
- Excel IFS Function: Formula, Examples, and TipsIn Excel, the IFS function checks conditions in order and returns the value paired with the first condition…
- COUNTIFS Does Not Contain: Excel Formula and ExamplesTo count cells that do not contain a particular text string in Excel, use a COUNTIFS criterion with…
- 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…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.