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!

 

[Eric W]

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

It's the IF. IF and IFERROR never worked inside of SUMPRODUCT without the CSE.

Marcelo, your formula fails with this case:

Book1
	A	B	C
1
2	B	Y	A
3	B	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
17
Sheet1

I've about got a non-CSE version working, but it's getting pretty long, and if the OP is happy, probably not worth it.

 

[Marcelo Branco]

Marcelo, your formula fails with this case:

oops...
Thanks for pointing out, but we can easily fix it.

=SUMPRODUCT(--(A2:A20=C2),--(MATCH(A2:A20&B2:B20,A2:A20&B2:B20,0)=ROW(B2:B20)-ROW(B2)+1))

M.