# (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