This tutorial explains how to count unique values in a range in Excel.

Sample Data

Suppose you have a column in Excel containing customers' names. You want to know how many customers are there in the list. Instead of manually going through the entire list and trying to count unique names, you can calculate using Excel formula.

Sample Data |

Formula

Suppose you have customers' names in cells B3 to B15.

=SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15))

Logic

The text "Jhonson" appears 3 times so we calculate the reciprocal of each of these counts. This results in the unique value being equal to(1/3) + (1/3) + (1/3) = 1. The same logic applies to all the other names. In the end, we will have the total number of unique values in a range.

How does formula work?

The **COUNTIF** function counts the number of times each value appears.

COUNTIF Formula Evaluation |

Then all the values are divided by 1 and **SUMPRODUCT**sums all the fraction values.

## Count Unique Values (Ignoring Blank Cells)

Data with Blank cells |

The following formula is used to calculate the unique count of non-blank values in the range B3:B15.

=SUMPRODUCT((B3:B15<>"")/COUNTIF(B3:B15,B3:B15&""))

See below how the formula works:

`(B3:B15<>"")`

checks for non-blank cells in the range**B3:B15**and returns a range of**TRUE**or**FALSE**values.`COUNTIF(B3:B15, B3:B15&"")`

calculates the count of each unique value in the range**B3:B15**concatenated with an empty string (**&""**). This calculates how many times each non-blank value appears.`(B3:B15<>"")/COUNTIF(B3:B15,B3:B15&"")`

divides the range of**TRUE**or**FALSE**values by the range of counts. It assigns a weight to each unique non-blank value.`SUMPRODUCT`

calculates the sum of the weights assigned to each unique non-blank value. This results in the distinct count of non-blank values in the range**B3:B15**.

## Post a Comment