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.
Countifs are not working.
ReplyDelete