Google Sheet Dynamic Sum

0
votes

I am using google sheets to make a spread sheet and do some simple math, I figured how to do the summing but the problem is that I have about 180 rows of data and want to avoid, if possible, the need to make a formula for every single pair of data. Here is the simple code that I have:

=SUM(AG4:AG5)

So I am writing this code in this case in AH4 and is always the same relative placement to the values I want to add. I want the sum of the two numbers one column to the left and the current row and a row under that. Is there any way to make it so that the same formula can be used over and over instead of typing each one out. Maybe some way to make the formula look one column to the left take that number and add it to the cell one column to the left and one row down?

Thanks for any help you can provide.

1
google-sheets

1 Answers

1
votes

You can use the ARRAYFORMULA function to apply a formula to multiple rows. It does not like some functions, though, and SUM() is one of them. So we need to use a different method to add the numbers. In this case, we just use AG4 + AG5. To apply the formula to all the rows in the spreadsheet we do a little more. Here is the formula, which would be placed in cell AG3 provided that is where the formula should start adding items.

=ARRAYFORMULA( IF( ISBLANK( AG3:AG),, AG2:AG + AG3:AG))

The IF ISBLANK AG3:AG) causes the formula to be applied to every row from row 3 to the last row in the sheet. ISBLANK will return FALSE on any row we want to work on so we provide nothing to teh TRUE portion of the IF statement. Note that I did not put "" in for the TRUE portion as that actually places a value in the row and can cause problems with other formulas. Since we are placing this in cell AG3 the addition will increment adding the row above to the current row.

EDIT from Comments

Placing this in cell AH2 will get you what you want:

=ARRAYFORMULA( IF( ISBLANK( AG2:AG),, IF(iseven( ROW(AG2:AG)),AG2:AG + AG3:AG,)))

Taking it a step farther, placing this in cell AH1 will label the header row for you and keep the formula out of the data rows. This has the advantage of allowing rows to be inserted above row 2.

=ARRAYFORMULA( IF(ROW(AH1:AH) = 1, "Total", IF( ISBLANK( AG1:AG),, IF(iseven( ROW(AG1:AG)),AG1:AG + AG2:AG,))))

The explanation is similar to above, only minor changes were made.

NOTE: The rest of column AH (below the formula) should not have any other manually entered data, so you will have to delete your formulas.