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 '0' or 'ref error'. i aim to count the number of times two items appear on the same row.
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.
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.
=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.
=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.
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
=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.
