# Difficulty in formulas

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

11 Replies

# Re: Difficulty in formulas

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.

# Re: Difficulty in formulas

@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.

# Re: Difficulty in formulas

=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.

# Re: Difficulty in formulas

=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.

# Re: 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

# Re: Difficulty in formulas

@OliverScheurich i don't understand this particular formula, and i can't edit it properly

# Re: Difficulty in formulas

=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.

# Re: Difficulty in formulas

=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.

# Re: Difficulty in formulas

Now i understand better. My dataset has 14964 rows and 11 columns

# Re: Difficulty in formulas

=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.

# Re: Difficulty in formulas

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 )
)``````