Count Unique values based on multiple columns

This tutorial explains how to count unique values based on multiple columns (or conditions) in Excel. In excel there is no straightforward way to calculate number of unique values. In general one removes duplicates in the data and then count the number of distinct values. This method is manual as you need to perform these two steps and then you will get your desired result. You can automate this task through nested formula by using SUMPRODUCT and COUNTIFS functions.

Sample Data

Sample Data

In the previous post we covered how to calculate number of unique values in a single column Here we are expanding the same method to multiple columns. See the formula below.

=SUMPRODUCT((1/COUNTIFS(B3:B15,B3:B15,C3:C15,C3:C15)))

Logic

The combination 1 and Jhonson appears 2 times so the unique value would be equal to (1/2) + (1/2) = 1

How Formula Works
COUNTIFS counts the number of times the values appear based on multiple criteria.

COUNTIFS Formula Evaluation

Then all the values are divided by 1 and SUMPRODUCT sums all the fraction values.
Count Unique Values (More than 2 columns) Here we want to calculate count of unique values based on 3 columns - Column B, C and D. See the sample data in the image below.

Sample Data

=SUMPRODUCT((1/COUNTIFS(B3:B15,B3:B15,C3:C15,C3:C15,D3:D15,D3:D15)))

Example 3 We are trying to answer "How many distinct combinations in each product based on Date and Campaign columns?" For example there are 8 rows in Product A but 6 unique values as per combination of Date and Campaign. We are looking for returned value of 6 against rows 2 through 9.

=SUM(IF($B$2:$B$18=B2,(1/COUNTIFS($A$2:$A$18,$A$2:$A$18,$B$2:$B$18,$B$2:$B$18,$C$2:$C$18,$C$2:$C$18)),0))

This is array formula so you need to press CTRL+ SHIFT + ENTER to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

Related Articles
1. 3 Ways to extract unique values
2. Count Unique values in a column
3. Select and Count Duplicate values in Excel

Spread the Word!
Share Share Tweet

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.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn