Count unique values
 

Hi

In my Excel worksheet there are two columns A & B. A contains the product
codes and B contains the customer codes. How can I count the number of UNIQUE
customer for a particular product? Both formula or pivot table would be fine.

Col A Col B
Pen Customer 1
Ruler Customer 1
Pen Customer 2
Ruler Customer 1
Pen Customer 1
Pen Customer 3

Thanks in advance!

Count unique values
 

Try the below array formula. Apply formula using 'Ctrl+Shift+Enter' instead
of 'Enter'

=SUM(IF(FREQUENCY(IF((B1:B10<"")*(A1:A10="Pen"),
MATCH(B1:B10,B1:B10,0)),ROW(A1:A10)-ROW(A1)+1),1))

--
Jacob


"Natalie Lau" wrote:

Hi

In my Excel worksheet there are two columns A & B. A contains the product
codes and B contains the customer codes. How can I count the number of UNIQUE
customer for a particular product? Both formula or pivot table would be fine.

Col A Col B
Pen Customer 1
Ruler Customer 1
Pen Customer 2
Ruler Customer 1
Pen Customer 1
Pen Customer 3

Thanks in advance!

Count unique values
 

Hello Nathalie,

I suggest to select a sufficiently long range with two columns and to
array-enter:
=Pfreq(A1:A999,B1:B999)
[Enter with CTRL + SHIFT + ENTER, not only with ENTER.]

Pfreq is a user-defined function:
http://sulprobil.com/html/pfreq.html

Press ALT + F11, enter a new module, copy my function (macro text)
into the new module, go back to your worksheet and the formula
mentioned above will work.

Regards,
Bernd

Count unique values
 

It works! Thank you!

Why using "Ctrl+Shift+Enter" instead of "Enter" please?

Count unique values
 

This is an array formula. An array formula can perform multiple calculations
and then return either a single result or multiple results. Array formulas
act on two or more sets of values known as array arguments. Each array
argument must have the same number of rows and columns. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula}"

--
Jacob


"Natalie Lau" wrote:

It works! Thank you!

Why using "Ctrl+Shift+Enter" instead of "Enter" please?

Count unique values
 

Try a little shorter Array_Formula:
http://img51.imageshack.us/img51/4248/nonamef.png
Micky



"Natalie Lau" wrote:

Hi

In my Excel worksheet there are two columns A & B. A contains the product
codes and B contains the customer codes. How can I count the number of UNIQUE
customer for a particular product? Both formula or pivot table would be fine.

Col A Col B
Pen Customer 1
Ruler Customer 1
Pen Customer 2
Ruler Customer 1
Pen Customer 1
Pen Customer 3

Thanks in advance!