Jun 15 2022 09:23 AM - edited Jun 15 2022 09:25 AM
Hi
I have a data set here , which looks similar to this
Order Id | apple | Banana | Orange | Kiwi | Total |
1 | 2 | 2 | |||
2 | |||||
3 | 2 | 1 | 3 | ||
4 | |||||
5 | 1 | 4 | 5 | ||
6 | 4 | 4 | |||
7 | 3 | 6 | 9 | ||
8 | 2 | 2 | 4 | ||
9 | 2 | 1 | 1 | 4 | |
10 | 4 | 5 | 9 | ||
Total | 11 | 11 | 11 | 7 | 40 |
The order Ids are unique and the numbers mentioned in the table are number of qty sold.
For example Order no 8 has 2 bananas and 2 oranges.
I need help in figuring out - Often Bought with - How many times apples were bought with oranges ? or how many times or orders have oranges and kiwi both ?
Jun 15 2022 12:25 PM - edited Jun 15 2022 12:35 PM
I am not sure whether there is a standard way of doing this? The formula returns the number of times each combination occurs.
The formula is a 365 array formula because I have pretty much forgotten traditional methods even when they may be simpler (more often they are not).
Jun 15 2022 12:35 PM
=SUMPRODUCT((G3:G12<>"")*(H3:H12<>""))
=SUMPRODUCT((I3:I12<>"")+(J3:J12<>""))
You can try these formulas for apples bought with bananas and for oranges or kiwi both.
Jun 15 2022 12:39 PM
Another way - see the attached workbook.
Jun 15 2022 02:31 PM
Works well. I did wonder about COUNTIFS, maybe within MAKEARRAY, but didn't get to pull it together. To understand the formula, I worked through it naming references as I recognised them (so making the formula comprehensible for me and incomprehensible for everyone else!)
= IF(@item1<>@item2,
COUNTIFS(
XLOOKUP(@item1,item,qty),">0",
XLOOKUP(@item2,item,qty),">0"
),
"-")
As for timing, the range of relative reference formulas appeared a bit slow first time through but the same as an array formula on subsequent trials.
Jun 15 2022 02:50 PM
Same solution again but this time using MAKEARRAY:
= MAKEARRAY(4,4,
LAMBDA(r,c,
IF(r<>c,
COUNTIFS(
INDEX(qty,,r), ">0",
INDEX(qty,,c), ">0"
),
"-"
)
)
)