Forum Discussion
Count unique text value with specific text criteria (For excel 2016 version)
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.
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.
HansVogelaar PeterBartholomew1
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.
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)?