Forum Discussion
Difficulty in formulas
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.
Now i understand better. My dataset has 14964 rows and 11 columns
=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 ) )
