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 ...
=SUMPRODUCT(((($A$2:$A$14964=R6)+($B$2:$B$14964=R6)+($C$2:$C$14964=R6)+($D$2:$D$14964=R6)+($E$2:$E$14964=R6)+($F$2:$F$14964=R6)+($G$2:$G$14964=R6)+($H$2:$H$14964=R6)+($I$2:$I$14964=R6)+($J$2:$J$14964=R6)+($K$2:$K$14964=R6))>0)*((($A$2:$A$14964=S6)+($B$2:$B$14964=S6)+($C$2:$C$14964=S6)+($D$2:$D$14964=S6)+($E$2:$E$14964=S6)+($F$2:$F$14964=S6)+($G$2:$G$14964=S6)+($H$2:$H$14964=S6)+($I$2:$I$14964=S6)+($J$2:$J$14964=S6)+($K$2:$K$14964=S6))>0))
Another alternative which works in Excel 2013 and in Office 365 is this formula.
As variant
=SUM(
(MMULT(--($A$2:$K$14964=R6), TRANSPOSE(--(COLUMN($A$1:$K$1)>0)))>0 )*
(MMULT(--($A$2:$K$14964=S6), TRANSPOSE(--(COLUMN($A$1:$K$1)>0)))>0 )
)