# Count Unique Values

S

#### 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

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)

=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

Biff,

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

Shane.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Shane Goodman said:
Biff,

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