Count uniques within duplicates

[ckramer]

Name	Product	Drink	# of Products	# of Unique Drinks
Joe	Sandwich	Coke	2
Joe	Sandwich	Diet	2
Jack	Salad	Coke	2
Jack	Dessert	Coke	2
Jane	Sandwich	Coke	3
Jane	Salad	Coke	3
Jane	Dessert	Diet	3

Hi,

I am looking to count the # of unique entries that show up only among duplicates.

Example:
Screen Shot 2013-01-24 at 1.03.47 PM.png

I'm trying to get the # of unique drinks that each person has? Joe should show 2 (coke and diet), Jack should show 1 (just coke) and Jane should show 2(coke and diet).
Thank you in advance.

[icestationzbra]

ARRAY formula in E2 (from your screenshot), drag-filled down:

Please Login or Register  to view this content.

solution to a similar issue here:

http://www.excelforum.com/showthread...=1#post3088377

UPDATED 25JAN13:

with attachment...

[ckramer]

Formula works for small spreadsheet, but when applying it to a 8000 rows sheet, excel poops out. Any advice on how to make it work? Excel for mac 2011

[nilesheth]

=SUM(1/COUNTIF(Range,Range)). Press Ctrl-Shift-Enter.