Excel : Count Unique Values Based on Multiple Columns

Deepanshu Bhalla 1 Comment

This tutorial explains how to count unique values based on multiple columns (or conditions) in Excel.

Excel doesn't have a simple way to count unique values. Usually, people remove duplicates and then count the remaining distinct values, which takes a lot of time and effort. You can automate this process by using the formulas like SUMPRODUCT and COUNTIFS.

Sample Data
Count Unique values based on multiple columns
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.

How to Count Unique Values Across 3 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

Here 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 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.

Post Comment 1 Response to "Excel : Count Unique Values Based on Multiple Columns"
Next → ← Prev