Count unique values

[Natalie Lau]

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!

 

[Jacob Skaria]

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

 

[Bernd P]

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

 

[Natalie Lau]

It works! Thank you!

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

 

[Jacob Skaria]

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>}"

 

[מיכ×ל (מיקי) ×בידן]

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