I have a countif conundrum. I have a sheet of data as shown below. I need to count the number of Food Groups where a specific Name has eaten at least one piece of Food of each unique type of Food listed for that Name in that Food Group.
For example, Jane had three pieces of fruit she could have eaten (two that were unique). She ate both unique types, so Fruit would count as 1. She also had two unique Vegetables, but only ate 1, so vegetable would not count. Jane's count is 1 food group.
Bob had one unique fruit he could have eaten and 1 unique meat he could have eaten and he ate both. The count for Bob would be 2 food groups.
Is it possible to have a formula that will return a Food Group count for Jane?
Name | Food Group | Food | Eaten? |
Jane Fruit Apple Yes
Jane Fruit Apple No
Jane Fruit Orange Yes
Jane Vegetable Lettuce Yes
Jane Vegetable Carrot No
Bob Fruit Apple Yes
Bob Meat Beef Yes
Bookmarks