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 ...
Formulas and Functions
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
Apr 18, 2024

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's avatar
    JoeAkpan28
    Copper Contributor
    Apr 18, 2024
    OliverScheurich i don't understand this particular formula, and i can't edit it properly
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Apr 19, 2024

      JoeAkpan28 

      =SUM((COUNTIFS(OFFSET($A$2:$K$2,ROW($1:$14963)-1,0),R6)>0)*(COUNTIFS(OFFSET($A$2:$K$2,ROW($1:$14963)-1,0),S6)>0))

       

      I've adjusted the formula according to the actual size of your data. The formula must be entered as an arrayformula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web.

       

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

       

      However if you work with Office 365 or Excel for the web you can apply the LAMBDA and MAP formula.

       

       

Resources