Forum Discussion

JoeAkpan28's avatar
JoeAkpan28
Copper Contributor
Apr 18, 2024

Difficulty in formulas

Good day, nice to have a nice community like this. 

I'm hooked on a task that i gave myself on market basket analysis, i have tried over 6 different combination of functions but i have been getting '0' or 'ref error'. i aim to count the number of times two items appear on the same row.

  

 

  • MAngosto's avatar
    MAngosto
    Iron Contributor

    JoeAkpan28 

     

    Hi,

     

    Could you specify your desired output?

     

    You wish to know how many rows are there with two columns of "ingredients"? That is, row 2 and 3 would not be counted but 4 and 5 would be?

     

    Thanks.

    • JoeAkpan28's avatar
      JoeAkpan28
      Copper Contributor

      MAngosto i want to count the number of times that two(2) independent products appear on the same row, and there are not just in 1 or 2 specific columns. they're scattered randomly in 11 columns.

      If you do help me, i would really appreciate.

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        JoeAkpan28 

        =SUM((COUNTIFS(OFFSET($A$2:$K$2,ROW($1:$8)-1,0),P9)>0)*(COUNTIFS(OFFSET($A$2:$K$2,ROW($1:$8)-1,0),Q9)>0))

         

        This formula works in my sheet. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

  • JoeAkpan28 

    =MAP(P9:P17,Q9:Q17,LAMBDA(fruit1,fruit2,SUM(BYROW(A2:K9,LAMBDA(r,(COUNTIFS(r,fruit1)>0)*(COUNTIFS(r,fruit2)>0))))))

     

    With Office 365 or Excel for the web you can apply this formula which spills the result.

    • JoeAkpan28's avatar
      JoeAkpan28
      Copper Contributor

      OliverScheurich  I really appreciate your efforts. Also, The formula doesn't work and then it highlights this particular r as if something is wrong with it

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        JoeAkpan28 

        =MAP(R6:R13,S6:S13,LAMBDA(fruit1,fruit2,SUM(BYROW(A2:K14000,LAMBDA(r,(COUNTIFS(r,fruit1)>0)*(COUNTIFS(r,fruit2)>0))))))

         

        =MAP(R6:R13,S6:S13,LAMBDA(fruit1,fruit2,SUM(BYROW(A2:K14000,LAMBDA(x,(COUNTIFS(x,fruit1)>0)*(COUNTIFS(x,fruit2)>0))))))

         

        In the attached file i've made an example according to the size of your database. From your screenshot i can't tell why the formula shouldn't work. You can try replacing r by x. Both formulas return the same result in my sample file.