To count distinct numeric values in a range in Microsoft 365 or Excel 2021, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,ISNUMBER(A2:A100)))),0), assuming the numbers are in A2:A100. The formula ignores blanks and text; for a count that includes distinct text values too, replace ISNUMBER(A2:A100) with A2:A100<>"".
What a unique-number count means
A unique-number count tells you how many different numeric values appear in a range, not how many cells contain numbers. If A2:A8 contains 12, 12, 15, 20, 20, 20, and 0, there are four distinct numbers: 0, 12, 15, and 20. Repeated values contribute only once to the total.
This is sometimes called a distinct count. It is useful for questions such as how many different product IDs, invoice numbers, score values, or dates are present. It is different from counting values that occur exactly once. In the example above, only 15 occurs once, but there are four distinct values overall.
Excel does not have a built-in worksheet function named COUNTUNIQUE. Instead, you can combine functions such as UNIQUE, FILTER, and ROWS, use a PivotTable, or use a compatibility formula. The best choice depends on your Excel version, whether you need the answer to update automatically, and whether you want to count only numbers or every kind of value.
Count distinct numbers with UNIQUE and FILTER
In Microsoft 365 and Excel 2021 or later, UNIQUE returns the different values from an array. FILTER can first remove blanks or nonnumeric cells, and ROWS counts the remaining distinct results. With numbers in A2:A100, enter this in an empty cell:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,ISNUMBER(A2:A100)))),0)
For example, if A2:A7 contains 4, 4, 9, a blank, the text word pending, and 0, the formula returns 3. FILTER keeps 4, 4, 9, and 0; UNIQUE reduces those to 4, 9, and 0; ROWS returns 3. IFERROR returns 0 if there are no numeric values to pass through the calculation.
UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later. The result is a dynamic-array calculation: Excel updates it when the source values change, and the formula normally needs only one cell. Use a bounded range such as A2:A1000 rather than a whole column when practical. Very large or full-column array calculations can take longer, particularly in workbooks with many formulas.
If the range contains both numbers and text and you want to count every distinct nonblank entry, use this version instead, assuming the data is in A2:A100:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)
This counts text entries as well as numbers. It still treats blank cells as excluded. Choose the numeric-only formula when text such as a note or status should not affect the answer.
Make Excel display the unique values too
You may want to inspect the values behind a count, not just see a total. UNIQUE can spill a list into cells below the formula. Assuming the numbers are in A2:A100, enter:
=UNIQUE(FILTER(A2:A100,ISNUMBER(A2:A100)))
Excel displays each distinct number once. To show the list in ascending order, assuming the same range, use:
=SORT(UNIQUE(FILTER(A2:A100,ISNUMBER(A2:A100))))
SORT, UNIQUE, and FILTER are available in Microsoft 365 and Excel 2021 or later. Make sure the cells where the result will spill are empty; otherwise Excel can show a #SPILL! error. The list is useful for checking whether an unexpected value, such as a stray zero or an unusually large entry, is included in the count.
Count all unique nonblank entries
Some lists mix numbers, text, and dates. If the aim is to count every distinct nonblank value rather than numeric values alone, use the nonblank formula above. For example, a column containing 101, 101, North, North, and 202 has three distinct nonblank entries: 101, North, and 202.
Be clear about what counts as blank. A truly empty cell is excluded by the test against an empty string. A formula that returns an empty string is also excluded by that test. A cell containing one or more spaces is not empty to Excel, so it may be counted as a distinct text value. If your source has inconsistent spacing, clean the data before counting or use a helper column to standardize it.
Dates are stored as serial numbers in Excel, so ISNUMBER treats genuine date values as numbers. Thus, the numeric-only formula counts distinct dates by their underlying values. If two cells show the same date but one includes a hidden time, they may be different serial values and count separately. Apply an appropriate date format to inspect the display, but remember that formatting alone does not change the stored value.
Use a PivotTable for a distinct count
A PivotTable can calculate a distinct count without a worksheet formula and is convenient for summarizing a large list. In Excel for Microsoft 365 on Windows, select the source data, choose Insert > PivotTable, and in the Create PivotTable dialog select Add this data to the Data Model before creating the PivotTable. Put the field you want to count in the Values area, open its value-field settings, and choose Distinct Count as the calculation.
The field may also be placed in Rows if you want to see the unique items themselves. Distinct Count is a Data Model calculation, so the option depends on creating the PivotTable with the data added to the model. If you do not see Distinct Count among the available summaries, check that setting rather than assuming the source values are unsuitable.
To update the PivotTable after changing the source data, use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. A PivotTable is a useful choice for grouped reporting—for example, counting distinct customer IDs by region—but it is less direct than a formula for a single live total. The Data Model and available PivotTable features differ across Excel platforms; if the option is unavailable in your edition, use the formula method or check that edition’s feature support.
Options for older Excel versions
Excel 2019 and earlier do not include the dynamic-array functions UNIQUE and FILTER. If you need a formula and are counting numbers only, FREQUENCY can count distinct numeric values. Assuming numbers are in A2:A100, use:
=SUM(--(FREQUENCY(A2:A100,A2:A100)>0))
FREQUENCY groups values into bins and returns a positive count for each bin that contains one or more numbers. Summing the TRUE results counts the occupied numeric bins, so duplicates add no extra count. Blank cells and text are not counted as numbers. In Excel 2019 or earlier, this is an array formula: confirm it with Ctrl+Shift+Enter rather than Enter alone. Excel shows braces around an array formula in the formula bar; do not type those braces yourself. In Microsoft 365 and Excel 2021 or later, Enter is sufficient.
This compatibility option is specifically for numeric values. It does not provide the same straightforward treatment of mixed text and numbers as the UNIQUE formula. If you need to count distinct text entries in an older version, a PivotTable or a helper column can be easier to maintain than a complex array formula. Test any legacy formula on a small sample where you know the expected answer.
Choose the method that fits the task
- Use UNIQUE with FILTER for a formula that updates as the source range changes, especially when you need to exclude blanks or separate numbers from text.
- Use a PivotTable when the count belongs in a report, needs grouping by another field, or should be refreshed alongside other summaries.
- Use FREQUENCY when working in an older Excel version and the values to count are numeric.
- Display a UNIQUE list when you also need to audit which values make up the total.
For a one-off count, a formula is usually quick to set up. For recurring analysis by category, a PivotTable can save time because it summarizes multiple groups in one view. For a shared workbook, consider which Excel versions the recipients use: a workbook that relies on dynamic arrays may not calculate those formulas in an older edition.
Common counting problems and checks
A result that seems too high or too low often reflects the contents of the source cells rather than a faulty count. Check these common causes:
- Numbers stored as text: A value that looks like 25 may be text, perhaps because it was imported with an apostrophe or spaces. The ISNUMBER filter excludes it. Convert the data to real numbers if it should count as numeric.
- Hidden decimals: Values displayed as 2.5 might actually be 2.49 and 2.51. They are distinct stored numbers even if formatting makes them look alike. Increase the displayed decimal places to inspect them.
- Spaces and inconsistent text: Extra spaces can make text entries different. A cell containing North and one containing North followed by a space may be treated as separate entries. Clean or standardize imported text before counting.
- Zeros and blanks: A real zero is a number and is counted. A blank is not. Do not remove zero from the data unless it is genuinely an invalid or missing-value marker in your dataset.
- Formula results: A formula returning an empty string looks blank, but it is not an empty cell. The FILTER test against an empty string excludes it; other methods may handle it differently.
For a quick check, display the unique list next to the count and compare the entries with the source. If the list includes a value you did not expect, inspect that cell’s formula bar and number format. A number format changes how a value appears, not the underlying value Excel uses when comparing numbers.
Frequently Asked Questions
Does Excel have a COUNTUNIQUE function?
No. Excel does not provide a standard worksheet function called COUNTUNIQUE. In Microsoft 365 and Excel 2021 or later, combine UNIQUE with ROWS, and use FILTER when you need to exclude blanks or limit the count to numbers.
How do I count numbers that appear only once?
That is different from counting distinct numbers: it counts values whose frequency is exactly one. If the data is in A2:A100, a Microsoft 365 formula is =SUMPRODUCT((A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)=1)). It ignores blank cells and counts each cell whose value occurs once, so two different one-off numbers add two to the result.
Does the formula count zero?
Yes. Zero is a numeric value, not a blank, so the numeric-only formula includes it once if it appears one or more times. If zero represents missing data in your sheet, decide whether to exclude it based on your data rules rather than treating every zero as blank automatically.
Can I count unique values in a filtered list?
The basic formulas examine the cells in the specified range, including rows hidden by a filter. They do not automatically restrict the count to visible rows. If the result must reflect only visible records, use a method designed for visible cells or build the filtered records into a separate range before applying UNIQUE.
Will the count update when I add more data?
It updates when the formula’s referenced range changes, but a fixed reference such as A2:A100 will not include entries typed in A101. Convert the source to an Excel Table with Insert > Table and use a structured table reference, or expand the formula range to cover the expected data. A PivotTable must be refreshed after source changes.
Related
- Commission Calculator Excel Template: Build One That Fits
- Redo in Excel: Keyboard Shortcuts and How It Works
- Excel Zoom Shortcut: How to Zoom In on a Worksheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Randomly Select From a ListWith names in A2:A20, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))) to return one randomly chosen entry. The formula selects a row position…
- Split Renal Function Test: What It MeasuresA split renal function test estimates how much each kidney contributes to the pair’s overall function. It is…
- Excel Length of Service Formula: Calculate Years and MonthsIf the start date is in B2 and the date to measure service through is in C2, use…
- Excel XLOOKUP With Multiple Criteria: Formulas & TipsTo use XLOOKUP with multiple criteria in Excel, multiply the criteria tests to require every condition to be…
- How to Use XLOOKUP in Excel: A Step-by-Step Formula GuideTo retrieve a value with XLOOKUP, identify the value to search for, the range where it appears, and…
- Inventory Tracking Spreadsheet: Setup, Formulas, and TipsAn inventory tracking spreadsheet is a worksheet that records what items you have, where they are, and when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.