Count Unique Values

[Shane Goodman]

Greetings,

I am really stuck would really appreciate some help.

I want to count the number of unique values in column B, but only if column
A contains a value.

A B
x abc
abc
x def
x
x ghi

Column B must also contain a value (not blank). So that means there are
three unique values in column B in the above table.

I was expecting to find a "count unique" function in Excel, but found
nothing.

Thanks!
Shane
Office Excel 2007 SP1

 

[T. Valko]

Try this array formula** :

=COUNT(1/FREQUENCY(IF(A2:A10<>"",IF(B2:B10<>"",MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-MIN(ROW(B2:B10)+1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Teethless mama]

=SUM(N(FREQUENCY(IF((A1:A10="x")*(B1:B10<>""),MATCH(B1:B10&"",B1:B10&"",)),MATCH(B1:B10&"",B1:B10&"",))>0))

ctrl+shift+enter, not just enter

 

[Shane Goodman]

Biff,

It worked! I have trouble understanding anything nested three or more times,
so I just copies it exactly and replaced the range values.

Thanks for your help.
Shane.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Biff,

It worked! I have trouble understanding anything nested three or more
times, so I just copies it exactly and replaced the range values.

Thanks for your help.
Shane.