Count Unique Values
 

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

Count Unique Values
 

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)

--
Biff
Microsoft Excel MVP


"Shane Goodman" wrote in message
...
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

Count Unique Values
 

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

ctrl+shift+enter, not just enter


"Shane Goodman" wrote:

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

Count Unique Values
 

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" wrote in message
...
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)

--
Biff
Microsoft Excel MVP

Count Unique Values
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Shane Goodman" wrote in message
...
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" wrote in message
...
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)

--
Biff
Microsoft Excel MVP