1

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.

Venue Cost Moving Moving Details AV Support Grand Total
1500 500 500 2500
2500 500 750 3750
1500 500 500 2500

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:

Venue Cost Moving Moving Details AV Support Grand Total
1500 500 500 4750
2500 500 750 5750
1500 500 500 4750

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:

Venue Cost Moving Moving Details AV Support Grand Total
1500 500 500 2500
2500 500 750 3750
1500 500 500 2500
#N/A
#N/A

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!

player0
  • 124,011
  • 12
  • 67
  • 124
lore
  • 13
  • 3

2 Answers2

0

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))

Martín
  • 7,849
  • 2
  • 3
  • 13
0

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)))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks, @player0! Thanks, @player0! I tried Example 2: Calculations were correct, but provided empty 0's in all the empty rows. Example 3 provides the desired result, but I'd like to understand it more. Previously, if I was using an array formula in a column, it's in the second row, and all of my column references begin at CxR2. This formula uses both R2 and just the column identifier. Is that intentional? THANKS! – lore Nov 10 '22 at 21:51
  • @LoreBurek answer updated. try 3rd formula and for understanding 4th formula see if this helps: https://stackoverflow.com/a/74281216/5632629 – player0 Nov 10 '22 at 22:06