Hi. Can you help? I've tried various iterations of pivot table arrangements but keep getting a count which is duplicated. The table is long and wide, but here's an example table:

 

NAME PURCH_ID SERVICE

MARY 1 apple

MARY 2 apple

MARY 3 apple

JEROME 4 orange

JEROME 5 orange

SUSAN 6 apple

SUSAN 7 orange

SUSAN 8 mango

 

I want to get a pivot table that provides unduplicated counts by consumer like this:

Apple 2 (Mary and Susan have apples)

Orange 2 (Jerome and Susan have oranges)

Mango 1 (Susan has mango)

 

I get a pivot table like this:

Apple     4

Orange  3

Mango  1

 

Thank you