Forum Discussion
Count unique text value with specific text criteria (For excel 2016 version)
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.
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!!!
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.
10 Replies
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.
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.
I have tried the formula in the excel, and the answer returns "1", is there anything i missed?
You must confirm the formula with Ctrl+Shift+Enter, otherwise it won't work as intended.
