Search code examples
google-sheetsgoogle-sheets-formulaarray-formulas

ARRAYFORMULA should be used only once in each formula or should it be used multiple times once for each need?


Sum the VLOOKUP results:

=ARRAYFORMULA(SUM(IFERROR(VLOOKUP(A1:A,B1:C,2,FALSE))))

Sum two cells:

=(Z1+Z2)

Sum two specific values from VLOOKUP:

=ARRAYFORMULA(SUM(IFERROR(VLOOKUP(G1:G2,H1:I,2,FALSE))))

Now I need to come up with an average of the three results:

=ARRAYFORMULA(
    SUM(IFERROR(VLOOKUP(A1:A,B1:C,2,FALSE)))+
    (Z1+Z2)+
    SUM(IFERROR(VLOOKUP(G1:G2,H1:I,2,FALSE)))
)/3

But the faithful form would be:

=(
    ARRAYFORMULA(SUM(IFERROR(VLOOKUP(A1:A,B1:C,2,FALSE))))+
    (Z1+Z2)+
    ARRAYFORMULA(SUM(IFERROR(VLOOKUP(G1:G2,H1:I,2,FALSE))))
)/3

Both will reach the same result, my question is, what is the most correct and safe way from the standards of those who work professionally with Google Sheet?

1 → Use only one ARRAYFORMULA call for the all the formula.

2 → Use multiple ARRAYFORMULA calls, one for each specific need.

Question reason:

I still haven't found risks of using a single ARRAYFORMULA in the beginning and doing everything else within it like =ARRAYFORMULA((...)+(...)) rather than =ARRAYFORMULA(...)+ARRAYFORMULA(...), but I not finding risks doesn't mean they don't exist.


Solution

  • One instance of ArrayFormula on the outside is both sufficient and the professional standard.

    However, I must say that I don't understand your formula usage or intention from your posted example. You've got + between each element, which is redundant to SUM. And like ArrayFormula, you only need one outer SUM to sum all elements in your usage.