Count unique values in column a where column b matches value

[AlbertRBrowne]

I need sum the amount of unique values in a column that match a value in another column.

a would contain say xxx, yyy, xxx
b Would contain bbb, ccc, bbb

I need to count the amount off times bbb appears but only once for xxx
I have this formula but it returns 0 and not the correct number.
=SUM(AND(H16:H50="bbb",(SUM(IF(FREQUENCY(IF(LEN(D16:D50)>0,MATCH(D16:D50,D16:D50,0),""),IF(LEN(D16:D50)>0,MATCH(D16:D50,D16:D50,0),""))>0,1)))))

Can anyone help thanks

Albert

 

[T. Valko]

I need sum the amount of unique values in a column that match a value in another column.

a would contain say xxx, yyy, xxx
b Would contain bbb, ccc, bbb

I need to count the amount off times bbb appears but only once for xxx
I have this formula but it returns 0 and not the correct number.
=SUM(AND(H16:H50="bbb",(SUM(IF(FREQUENCY(IF(LEN(D16:D50)>0,MATCH(D16:D50,D16:D50,0),""),IF(LEN(D16:D50)>0,MATCH(D16:D50,D16:D50,0),""))>0,1)))))

Can anyone help thanks

Albert

Try this...

Book1
	A	B
2	BBB	XXX
3	BBB	YYY
4	BBB	XXX
5	BBB	GGG
6	BBB	GGG
7	BBB	YYY
8	BBB	YYY
9	BBB	DDD
10	BBB	GGG
Sheet1

This array formula**:

=SUM(IF(FREQUENCY(IF(A2:A10="BBB",MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range of column B.

 

[AlbertRBrowne]

Thanks for the reply works ok

 

[T. Valko]

Thanks for the reply works ok

You're welcome. Thanks for the feedback!