Need to find formula to find value and add all data without the duplicates

[sneakyla]

Hello,

I need to find the correct formula to find a specific value such and sum all the sizes without any duplicates. Please see below for the table example.

Basically, what I need to achieve is a formula to find BANANA and give a total size number of 3 because banana has small medium and large (3 different sizes), same goes for APPLE with a returning value of 3 because there 3 different sizes for apple too. If I added values of ORANGE with only Small and Medium then I need the formula to return the value of 2 because there are only 2 different sizes.

Thank you!

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	A	X	A	3
3	A	X
4	A	Y
5	A	Y
6	A	Z
7	A	Z
8	A	Z
9	B	X
10	B	X
11	B	Y
12	B	X
13	B	Z
14	B	Z
15	B	Z
16	B	X
Main

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(IF(A2:A20=C2,1/COUNTIFS(A2:A20,C2,B2:B20,B2:B20))))

 

[sneakyla]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	A	X	A	3
3	A	X
4	A	Y
5	A	Y
6	A	Z
7	A	Z
8	A	Z
9	B	X
10	B	X
11	B	Y
12	B	X
13	B	Z
14	B	Z
15	B	Z
16	B	X
Main

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(IF(A2:A20=C2,1/COUNTIFS(A2:A20,C2,B2:B20,B2:B20))))

Hello!

This one didn't work.

 

[Fluff]

In what way?

 

[sneakyla]

In what way?

I used your formula and it was supposed to give me a value of 8 but instead gave me 0.33333333

 

[Eric W]

In what way?

Fluff, for anyone without 365, your formula requires CSE (Control+Shift+Enter).

 

[Fluff]

Thanks for that Eric, didn't think it would need it with sumproduct.

 

[sneakyla]

It definitely worked with CSE. Thank you so much Eric and Fluff!!

 

[Marcelo Branco]

Or
=SUMPRODUCT(--(A2:A20=C2),--(MATCH(B2:B20&"",B2:B20&"",0)=ROW(B2:B20)-ROW(B2)+1))
confirmed with just Enter

M.

 

[Fluff]

Glad we could help & thanks for the feedback.