How to find multiple co-relations

[Pritu650]

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 ?

[#2]

@Pritu650 

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).

[#3]

@Pritu650 

=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.

 

[#4]

@Pritu650 

Another way - see the attached workbook.

[#5]

@Hans Vogelaar 

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.

[#6]

@Hans Vogelaar 

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"
            ),
        "-"
        )
     )
  )