Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count unique values
It works! Thank you!
Why using "Ctrl+Shift+Enter" instead of "Enter" please? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
count unique values, if ... | Excel Worksheet Functions | |||
How to count unique values? | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Count Unique Values | Excel Worksheet Functions |