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

Questions

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

May 17, 2022

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 are applying SUM to a VLOOKUP in two places; but your VLOOKUP returns only one value, so there is nothing to SUM. You’ve also got the redundant + between each element. So again, I see no reason for SUM in your example formula. By using it, you’re just saying, "Sum everything in this ‘group’ that contains only one item."