How to do summation of unique counts based on another variable?

[Chin Huat]

This is an extract of my source data with thousands of records. I want
to know the no. of cutomers (unique count) associated with each
product without deleting any records from the source file. Is there
any formula/function which I can use? E.g For product 2, there are 4
unique customers while product 4 has only 1 unique customers?

Customer 1 Product 1
Customer 2 Product 2
Customer 7 Product 2
Customer 5 Product 2
Customer 3 Product 2
Customer 2 Product 2
Customer 2 Product 3
Customer 3 Product 4
Customer 4 Product 4
Customer 4 Product 4

 

[T. Valko]

source data with thousands of records.

Depending on how many records this could be slow to calculate.

Assuming your data is in the range A2:B11 with no empty/blank cells...

D2n = Product 1, Product 2, etc.

Enter this array formula** in E2 and copty down as needed:

=SUM(IF(FREQUENCY(IF(B$2:B$11=D2,MATCH(A$2:A$11,A$2:A$11,0)),ROW(A$2:A$11)-ROW(A$2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Ashish Mathur]

Hi,

You may also try this. After the source data range, insert another column
(third one) where you type in 1 in all the cells till the last row till
where your data is (Give it a heading, say numbers). Now create a pivot
table and drag product to the row area. Now drag customer to the row area
(should appear after product). In the data area drag, the column of
numbers. Now, on the pivot table toolbar, click on the pivot table button
and under formulas, select calculated field. Type a name of your choice and
the formula should be =numbers^0. Now right click in the data area and
under value filed settings, under Show data as, select Running total in and
the select Customer.

You will now see the unique count.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bernd P]

Hello Chin,

Select a sufficiently long range with 2 colums and array-enter:
=Pfreq(Pfreq(B1:B999,A1:A999))


Pfreq you can find here:
http://sulprobil.com/html/pfreq.html


Regards,
Bernd

PS: Please do not cross post.