Forum Discussion
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 ...
OliverScheurich i don't understand this particular formula, and i can't edit it properly
=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.
