How to Count Unique Values in a Column in Excel

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

Sample Data

Imagine you have a column in Excel containing customers' names. You want to know how many customers are there in the list. This is one of the scenarios where counting unique values is required. Instead of manually going through the entire list and trying to count unique names, Excel can help you to calculate it quickly in an automated fashion.

Sample Data in Excel for Counting Unique Values
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 Function
COUNTIF Formula Evaluation

Then all the values are divided by 1 and SUMPRODUCTsums all the fraction values.

Count Unique Values (Ignoring Blank Cells)

Excel : Count Unique values in a column
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:

  1. (B3:B15<>"") checks for non-blank cells in the range B3:B15 and returns a range of TRUE or FALSE values.
  2. 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.
  3. (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.
  4. 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.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

0 Response to "How to Count Unique Values in a Column in Excel"

Post a Comment

Next → ← Prev
Looks like you are using an ad blocker!

To continue reading you need to turnoff adblocker and refresh the page. We rely on advertising to help fund our site. Please whitelist us if you enjoy our content.