SUMPRODUCT and unique values

[gjs1985]

Hi,

Please see below a sample fairly basic data set that I am working on:



What I would like to do is return the number of unique values from column b for Susan. By using the SUMPRODUCT formula as shown it results in 5 which is the total number of values but I’m wanting to get it to show the correct number of 3.

I think it may just be a slight variance on the formula that is required and would be grateful for any ideas.

Thanks.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[gjs1985]

I’m using 2013 and have updated this on my account.

 

[Fluff]

Thanks for that.
How about

+Fluff v2.xlsm
	A	B	C	D
1
2	Susan	A	Susan	3
3	John	E
4	Susan	B
5	Susan	C
6	Paul	F
7	Susan	A
8	John	G
9	Susan	C
10
Main

Cell Formulas
Range	Formula
D2	D2	=SUM(--(FREQUENCY(IF($A$2:$A$9=C2,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JoeMo]

Here's an alternative using SUMPRODUCT:

Book1.xlsm
	A	B	C	D
2	Susan	A	Susan	3
3	John	E
4	Susan	B
5	Susan	C
6	Paul	F
7	Susan	A
8	John	G
9	Susan	C
Sheet5

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT((B2:B9<>"")*(A2:A9=C2)/COUNTIF(B2:B9,B2:B9&""))