Sumif, unique counts,

[Chin Huat]

Hi Excel Gurus;

I have another problem. I have 2 worksheets - data source and a
summary worksheet.

The source data worksheet has the following data :

A B
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

In the summary worksheet, I have the following summary worksheet:-

Type of Product No. of unique customers who bought
Product 1
Product 2
Product 3
Product 4

What formula can I use in the summary worksheet to derive the no. of
unique customer counts for each product. E.g for product 2, there are
5 records but there are only 4 unique customers? I tried sumif but dun
know how to get the formula to do unique count only. Appreciate your
advise. Thanks.

 

[dlamarche]

Hello Chin

Sometimes a simple solution is the best solution.

I did the following using a pivot table in less than 30 seconds.

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

Is that what you wanted? Maybe I did no understand your question.

The Pivot Table can also detail by products. Here I removed the total per
row and column for simplicity.

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

Did that help?

 

[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

 

[Domenic]

Assuming that A2:B11 contains the data, and D25 contains Product 1,
Product 2, etc., try...

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($B$2:$B$11=D2,IF($A$2:$A$11<>"",MATCH("~"&$A$2:$A$11
,$A$2:$A$11&"",))),ROW($A$2:$A$11)-ROW($A$2)+1),1))

 

[T. Valko]

see your other post