(Excel examples for Beginners)
In this end-to-end excel example, you will learn – How to Count unique numeric values in a range in Excel.
Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.
Western Australian Center for Applied Machine Learning & Data Science – Membership
How to Count unique numeric values in a range in Excel
Generic formula
=SUM(--(FREQUENCY(data,data)>0))
Explanation
To count unique numeric values in a range, you can use a formula based on the FREQUENCY and SUM functions. In the example shown, employee numbers appear in the range B5:B14. The formula in F5 is:
=SUM(--(FREQUENCY(B5:B14,B5:B14)>0))
which returns 4, since there are 4 unique employee ids in the list.
How this formula works
The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of numeric values, organized in “bins”. We use it here as a roundabout way to count unique numeric values.
Working from the inside-out, we supply the same set of numbers for both the data array and bins array to FREQUENCY:
FREQUENCY(B5:B14,B5:B14)
FREQUENCY returns an array with a count of each numeric value in the range:
{4;0;0;0;2;0;1;3;0;0;0}
The result is a bit cryptic, but the meaning is 905 appears four times, 773 appears two times, 801 appears once, and 963 appears three times.
FREQUENCY has a special feature that automatically returns zero for any numbers that have already appeared in the data array, which is why values are zero once a number has been encountered.
Next, each of these values is tested to be greater than zero:
{4;0;0;0;2;0;1;3;0;0;0}>0
The result is an array like this:
{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}
Each TRUE represents a unique number in the list. The SUM ignores logical values by default, so we coerce TRUE and FALSE values to 1s and 0s with a double negative (–), which yields:
=SUM({1;0;0;0;1;0;1;1;0;0;0})
Finally, SUM adds these values up and returns the total, which in this case is 4.
Note: you could also use SUMPRODUCT to sum the items in the array.
Using COUNTIF instead of FREQUENCY to count unique values
Another way to count unique numeric values is to use COUNTIF instead of FREQUENCY. This is a simpler formula, but beware that using COUNTIF on larger data sets to count unique values can cause performance issues. The FREQUENCY formula, while more complicated, calculates much faster.
Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.
Western Australian Center for Applied Machine Learning & Data Science – Membership
Pandas Example – Write a Pandas program to create a time series using three months frequency
Python Example – Write a Python program to get the frequency of the elements in a list