Count of (distinct) values belonging to a list

[Anonymous]

Hi,

I have the following data:

Name	Food
John	Apple
John	Bread
John	Apple
Mary	Banana
Mary	Butter
Mary	Bread
Mary	Orange
Dan	Bread
Dan	Butter

List of fruits:

Fruits
Apple
Banana
Orange

How can I get the count of distinct fruits pro Person? Like below:

Name	Count of Fruits
John	1
Mary	2
Dan	0

Thanks a lot for your help.

[andrey_krylov · Accepted solution]

Try this

=Count({< Food = {$(=Chr(34)&Concat(Distinct Fruits,'","')&Chr(34))} >} TOTAL <Name> Distinct Food)