Count multiple

[hocean]

Hi,

I'm very new to reporting and have been tasked with finding out the combinations of products that have been bought by multiple clients, and their frequency. There are 6 products in total and every client has bought at least 2, maximum 4.

Each row is a purchase (product, client) and there are 408 in total.

So ideally I would like to find out every combination that appears e.g Product 1 and Product 2, and how many times this combination has been purchased - Basically I need to find out what our most popular product combinations are.

I could do this manually however this list will keep getting longer!

Any help and guidance would be incredibly appreciated!!

Thank you

[Sam Capricci]

here is your example reattached giving you two different formulas you can use, one for a count by client (BTW you have an extra space for "Client 1 " that I accounted for but you should remove).
The other formula is to return a unique list of products by client that they purchased.
I put the client names in row 1 beginning in cell D1
formula 1 =COUNTIF($B$2:$B$12,D1) then drag right
formula 2 =IFERROR(INDEX($A$2:$A$12,AGGREGATE(15,6,ROW($A$1:$A$12)/($B$2:$B$12=D$1),ROWS($A$2:A2)),COLUMN()-(COLUMN()-1),1),"") drag down until returns blanks, then drag right

[hocean]

Thank you so much for this!

Do you know how I would now be able to count the number of times the combination appears, so regardless of the client? So, there are 189 clients, each with a combination of products bought. Would it be possible to see:

1) How many combinations of products bought there are (e.g Product 1 + Product 6, Product 3 + Product 4 + Product 6)
2) How many times each combination of products was bought? (e.g Product 1 + Product 6 was purchased by 7 clients, Product 3 + Product 4 + Product 6 was purchased by 12 clients)?

I've reattached your example - any help would be great!

Thanks again!

[Sam Capricci]

how many products do you have? Because the new requirements (1 and 2 in post#3) of your request would require a matrix - potentially an enormous combination of values. For example, someone can buy 1 and 6, another 1 and 5, another 1 and 4, another 1 and 3, another 1 and 2 and another 1 only. Then 1,2 and 6 and another 1, 2 and 5, the combinations could be quite large.

[Glenn Kennedy]

I'm not REALLY sure what you want... but how does this array formula look, in combination with the drop-down box to its left.

=SUMPRODUCT((MMULT(COUNTIFS(B2:B12,B2:B12,A2:A12,TRANSPOSE(Combo)),ROW(Combo)^0)>=2)/COUNTIF(B2:B12,B2:B12))