Ever wonder how to count the number of unique numbers in a column without using PivotTables or helper columns?
Here’s a classic Excel array formula that does the job beautifully:
|
1 |
=SUM(IF(FREQUENCY(A:A, A:A) > 0, 1)) |
How It Works
-
FREQUENCY(A:A, A:A)
Returns an array where only the first occurrence of each number shows how many times it appears. All duplicates return 0. -
IF( … > 0 , 1 )
Converts every non-zero count into a single 1, ignoring duplicates. -
SUM( … )
Adds those 1’s to give you the total number of distinct numeric values.
Example
If column A contains 5, 5, 7, 7, 7, 9, the formula returns 3—because there are three unique numbers: 5, 7, and 9.
Tips
-
Works on numbers only (text is ignored).
-
Blank cells are skipped.
-
In Excel 365/2021 you can enter it normally; in older versions press Ctrl + Shift + Enter.
With this single formula, you can quickly spot how many unique numeric entries are in any dataset—perfect for dashboards, reports, or quick checks.
Last Updated on 2025-09-22 by gantovnik
Recent Comments