Count unique values in one column based on conditions and unique values in another column

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

Hi Margaret Anne,

What you are asking is counter-intuitive. I don't understand why Jane doesn't get 2 for Fruit and 1 for vegetables.

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,

Thanks for responding. The counter is for Food Group, not Food. Jane only gets a +1 count for a food group if she ate at least one piece of each unique type of food from that food group that she could have eaten. So she gets a +1 for the Fruit food group, since she ate one apple and one orange and there were no other unique fruits she could have eaten. She does NOT get a plus one for Vegetable, because she only ate one of the two unique vegetables.

Bob gets a +1 for meat and a +1 for fruit because he only had one unique meat and one unique fruit and he ate both.

Does that make more sense?

Margaret