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
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.
COUNTIFS counts the number of times the values appear based on multiple criteria.
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.
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.
Related Articles
1. 3 Ways to extract unique values
2. Count Unique values in a column
3. Select and Count Duplicate values in Excel
Sample Data
 |
| Sample Data |
=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) = 1How Formula Works
COUNTIFS counts the number of times the values appear based on multiple criteria.
 |
| COUNTIFS Formula Evaluation |
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
About Author:
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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
Countifs are not working.
ReplyDelete