May 14 2021 07:44 AM
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!!!
May 14 2021 08:07 AM
SolutionIn 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.
May 14 2021 08:22 AM
I have tried the formula in the excel, and the answer returns "1", is there anything i missed?
May 14 2021 08:37 AM
You must confirm the formula with Ctrl+Shift+Enter, otherwise it won't work as intended.
May 14 2021 01:11 PM
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.
May 16 2021 09:24 PM
May 17 2021 11:50 PM
@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.
May 18 2021 01:37 AM
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)?
May 19 2021 06:44 PM
May 20 2021 01:31 AM
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.
May 20 2021 07:40 PM
May 14 2021 08:07 AM
SolutionIn 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.