Search code examples
arraysgoogle-sheetsmultidimensional-arraygoogle-sheets-formulaarray-formulas

Can't use SUM inside ARRAYFORMULA

I just confused about what I'm doing wrong I use ARRAYFORMULA just to sum every range in a row like this

={
"Manager Result";
ARRAYFORMULA(
SUM(D2:D,H2:H)
)
}

But I get this instead

enter image description here

When I use it like this

={
"Manager Result";
ARRAYFORMULA(D2:D+H2:H)
}

Everything is OK

enter image description here

But I still can't get why I can't use SUM inside ARRAYFORMULA.

The second trouble is I'd like to place the same number in one column but it also doesn't work so I get an error:

"Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings."

    ={
"Max Result 
(Basic --> total count of 3 marks)";
ARRAYFORMULA(J2:J=15)
}

enter image description here

Any ideas? Thanks!

Solution

  • SUM is not supported under ARRAYFORMULA. you can either use ranges like you did D2:D+H2:H or use MMULT function which is able to perform all 4 kinds of operation + - ÷ ×

    in your case it would be:

    ={"Manager Result"; ARRAYFORMULA(MMULT({A2:A6, C2:C6}, {1;1}^0))}

    enter image description here

    see: https://infoinspired.com/google-docs/spreadsheet/mmult-function-in-google-sheets/