CODEWITHSUNDEEP
google-sheets
numbers garden
numbers garden

Reputation: 11

Count of most frequent combinations sold

I'm referencing this older question, but I am having trouble updating the formula for my needs as my column and rows are switched and my data set has too many rows to transpose to make his formula work properly (google sheets or numbers)

Count which combination of items are bought most frequently

The formula in this picture is not quite working for me.

table image

=SUMPRODUCT($E3:$H3,INDEX($E$3:$H$12,MATCH(K$2,$D$3:$D$12,0),0))

How would I get that to work if my data was the same, but column and rows were switched? To be more specific, I have items as the header, and order numbers as rows (I have > 15k rows).

What I'm trying to do is take a large set of order data and find which combinations of products people are ordering most often. My initial search led me to creating the matrix as posted above. That would totally work for output, I'm just not able to get a formula to work using the other posts I've read.

Maybe a better example of what output may look like?enter image description here

Upvotes: 1

Views: 188

Answers (1)

Bryan Monterrosa
Bryan Monterrosa

Reputation: 1470

I came up with this solution:

=SUMPRODUCT(E$3:E$100,INDEX($3:$100,0,MATCH($Q2,$2:$2,0)))

It would work like this:

enter image description here

Important: For this solution to work you'd need to change the range that contains the data. For example, the data reaches row 1500:

=SUMPRODUCT(E$3:E$1500,INDEX($3:$1500,0,MATCH($Q2,$2:$2,0)))

Upvotes: 1

Related Questions