Forum Discussion
JoeAkpan28
Apr 18, 2024Copper Contributor
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 ...
MAngosto
Apr 18, 2024Iron Contributor
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.
- JoeAkpan28Apr 18, 2024Copper 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.
- OliverScheurichApr 18, 2024Gold Contributor
=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.
- JoeAkpan28Apr 18, 2024Copper ContributorOliverScheurich i don't understand this particular formula, and i can't edit it properly