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 |
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)))
The combination 1 and Jhonson appears 2 times so the unique value would be equal to (1/2) + (1/2) = 1
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.
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)))
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.
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.
Countifs are not working.
ReplyDelete