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