count unique values in one column for a set value in another col.

[Jean-Luc]

I am trying to count how many unique values (text) I have in one column, for
the row cases where a particular value exists in another column.
Perhaps some combination of Frequency function with Countif or Sumproduct...?

 

[Ron Coderre]

With A1:B10 containing
Ref______Code
1000_____AAA
1000_____BBB
1000_____AAA
2000_____BBB
2000_____AAA
3000_____DDD
4000_____AAA

and...
D1: a code to find unique values for....eg AAA

This regular formula returns the count of unique Refs where Code=AAA
=SUMPRODUCT(--(FREQUENCY((B1:B10=D1)*MATCH(A1:A10&"_",A1:A10&"_",0),(B1:B10=D1)*
MATCH(A1:A10&"_",A1:A10&"_",0))*(B1:B11=D1)>0))

In the above example, that formula returns: 3

Is that something you can work with?

 

[Ron Coderre]

This shorter formula seems to do the same job:
=SUMPRODUCT((MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0)=(ROW(A2:A10)-ROW(A1)))*(B2:B10=D1)

 

[LizG]

I am trying to count how many unique values (text) I have in one column, for
the row cases where a particular value exists in another column.
Perhaps some combination of Frequency function with Countif or Sumproduct...?

 

[LizG]

I think you are looking for the SumIf command. Do a search for SumIf under
the help menu. I am pretty sure this will answer your question.