I have a spreadsheet (Google Sheets) with several non-contiguous Cost columns. When I try to user ArrayFormula to sum each row "Grand Total", it's returning some interesting (incorrect) numbers.

Here's a sample, stripped down the number of cost columns for brevity.

My Array Formula for the Grand Total Column is

=ARRAYFORMULA(filter(G2:G, G2:G<>"") SUM(H2:H,J2:J))

where G is the Venue Cost and H and J are additional costs.

This is the resulting Grand Total:

I tried moving away from SUM, to just manually adding the additional costs together.

=ARRAYFORMULA(filter(G2:G, G2:G<>"") (J2:J H2:H ))

but received the #N/A error, which expanded to "Error: Array arguments to ADD are of different size." We have a document generating tool that sees those N/A's as additional jobs to process, so any extra spillover is not good:

I'm pretty sure that the SUM and the ADD versions are including more than one row, do I need to filter the 'Additional Costs' Columns as well as the Venue Costs? Or, are the non-contiguous columns an issue?

Thank you!

CodePudding user response：

I don't know if I fully understand what you're trying to accomplish, but try this:

=ARRAYFORMULA(IF(G2:G="","",G2:G H2:H J2:J))

CodePudding user response：

try:

=INDEX(IF(G2:G="",,G2:G H2:H J2:J))

or:

=BYROW({G2:H, J2:J}, LAMBDA(x, SUM(x)))

or:

=BYROW({G2:H, J2:J}, LAMBDA(x, IFERROR(1/(1/SUM(x)))))

or:

=BYROW({G2:INDEX(H:H, COUNTA(G:G)), J2:INDEX(J:J, COUNTA(G:G))}, LAMBDA(x, SUM(x)))