SOLVED

Count unique text value with specific text criteria (For excel 2016 version)

Copper Contributor

Hello All, 

 

I have a case to solve the total no. of food type by respective person as shown below, find out the total no. of food type owned by "Tom" & "Jane" without duplicating the food name.

 

螢幕截圖 2021-05-14 下午10.40.29.png

I have tried several combined functions like sumif, countifs, sumproduct, but all doesn't work, May I know if there's any way to achieve the outcome with a single formula (without using pivot table). Many thanks!!!

10 Replies
best response confirmed by Diem_Carpe (Copper Contributor)
Solution

@Diem_Carpe 

In D2 confirmed with Ctrl+Shift+Enter:

 

=SUM(IF($A$2:$A$16=C2,1/COUNTIFS($A$2:$A$16,C2,$B$2:$B$16,$B$2:$B$16)))

 

Fill down.

@Hans Vogelaar 

 

I have tried the formula in the excel, and the answer returns "1", is there anything i missed?

螢幕截圖 2021-05-14 下午11.18.26.png

@Diem_Carpe 

You must confirm the formula with Ctrl+Shift+Enter, otherwise it won't work as intended.

@Diem_Carpe 

This is of no use whatsoever for the OP but others may be interested in the changes that Excel 365 is bringing to spreadsheets.  First a traditional nested form

= COUNTA( UNIQUE( FILTER(Food, Name=@DistinctName) ) )

.  A sequential alternative is  

= LET(
    SelectedFood, FILTER(Food, Name=@DistinctName),
    DistinctFood, UNIQUE(SelectedFood),
    COUNTA(DistinctFood) )

 which is more verbose but can be read without reference to the spreadsheet itself.

@Hans Vogelaar @Peter Bartholomew 

 

Further to my question, May I know if there's any way to modify the function so that it allows the formula to count the blank cell as well or ignore the rows which cannot match column A and Column B.

 

Diem_Carpe_0-1621320273181.png

 

@Diem_Carpe 

You write "it allows the formula to count the blank cell". Do you mean that blank counts as a food type? So for example, the count for Jane would be 3 ("Apple", "Melon", blank)?

Sorry for the misleading description. the outcome shall skip counting the blank cell, e.g. Jane would be 2 ("Apple" & "Melon", but an error "#DIV/0!" was shown when i use the same formula.

@Diem_Carpe 

In D2 confirmed with Ctrl+Shift+Enter:

 

=SUM(IF(($A$2:$A$16=C2)*($B$2:$B$16<>""),1/COUNTIFS($A$2:$A$16,C2,$B$2:$B$16,$B$2:$B$16)))

 

See the attached version.

1 best response

Accepted Solutions
best response confirmed by Diem_Carpe (Copper Contributor)
Solution

@Diem_Carpe 

In D2 confirmed with Ctrl+Shift+Enter:

 

=SUM(IF($A$2:$A$16=C2,1/COUNTIFS($A$2:$A$16,C2,$B$2:$B$16,$B$2:$B$16)))

 

Fill down.

View solution in original post