Google-sheets – Compute the total of every row with arrayformula

formulasgoogle sheets

In my spreadsheet, I need to add up each individual row. It does that if I drag the formula down in Y, but then when a new row is added to the data from a Google form, it won't copy the formula. So I need an arrayformula for the sum.

I have attached a sample sheet for the data that I am using. I have managed to get the arrayformulas to work for all except for column Y. As you can see from the formula I need it to total J2:P2, and here is where I am experiencing problems with my array.

Sample Data set

Best Answer

The main goal is to make sum ArrayFormula. The original brilliant solution was provided here.

And your formula is:

=ArrayFormula(
IF($Q2:Q="No",
  SUMIF(IF(COLUMN(J2:P2),ROW(A2:A)),ROW(A2:A),J2:P),
IF($Q2:Q="Yes",
  SUMIF(IF(COLUMN(R2:X2),ROW(A2:A)),ROW(A2:A),R2:X),
IF($Q2:Q="",""))))
Related Topic