How can I apply an Arrayformula for multiple cells on Google Sheets which includes fixed and variable cells?

Question

I want to apply an arrayformula to my worksheet on Google Sheets, which works good when I drop-down the formula to the cells below. But because I have quite much data, I need to use the arrayformula for this formula and I can't find a solution how to apply the variable cell (in this example B11 and C11) to all cells below it.

The screenshot should explain my problem very well.

=COUNTIF((ARRAYFORMULA(IF((ARRAYFORMULA(IF(B11>$B$4:$B$7,IF(C11>$C$4:$C$7,1,0),0)))=1,IF((ARRAYFORMULA(IF($K$4:$K$7>$J$4:$J$7,1,0)))=1,1,0),0))),"1")

Here a link to my file:

https://docs.google.com/spreadsheets/d/1c17IQCujy3cQwDOcbJUpm3iCgJHCbD8QRbK0aQfVtQA/edit?usp=sharing

The output is in the green field

player0 player0 · Accepted Answer · 2019-10-08T20:10:54

it would be like this:

=ARRAYFORMULA(MMULT(
 IF(IF(INDIRECT("B11:B"&COUNTA(B11:B)+10)>TRANSPOSE(B4:B7),
    IF(INDIRECT("C11:C"&COUNTA(C11:C)+10)>TRANSPOSE(C4:C7), 1, 0), 0)=1,
 IF(IF(TRANSPOSE(K4:K7)>TRANSPOSE(J4:J7), 1, 0)=1, 1, 0), 0), {1; 1; 1; 1}))

How can I apply an Arrayformula for multiple cells on Google Sheets which includes fixed and variable cells?

1 Answers