0

Happy 2022, all!

I have a table in which I'd like to have a totals row where the numbers from each column are summarized.

The formula below works for 2 rows (and all following columns with text in Row 2) =ArrayFormula(IF(COLUMN(2:2)=1,"TOTAL",IF(LEN(2:2)>0,A3:3+A4:4,"")))

The formula below works for 3 rows (and all following columns with text in Row 2) =ArrayFormula(IF(COLUMN(2:2)=1,"TOTAL",IF(LEN(2:2)>0,A3:3+A4:4,""))) etc,

I'd like to set up a formula which I do not have to modify every time I add a row because that's both time-consuming and likely to lead to errors. Another important preference is to be able to define a heading. I am currently using IF(COLUMN(2:2)=1,"TOTAL" to do that but if the new formula requires a different solution, that's perfectly fine.

Sample file: https://docs.google.com/spreadsheets/d/1YdV2rmvdfoFMcdPKPEmNlUWJtvzPE7-5nU1f9hIv4XU/edit#gid=0

Apologies if this question has already been answered. In that case please link to the existing question/answer, if possible. I looked and didn't find anything that worked.

Thank you

0

This problem is complicated by requiring the formula to be in the same column as the data it is summing. But as an accountant in a past life, I know exactly why you would want/need/prefer it like this.

Option#1 - Insert new rows above

  • This is the KISS solution

  • In the first blank row at the bottom of the column A, enter "Insert new rows above"

    • the goal is that any new rows are to be inserted ABOVE this row.
  • In the next blank row, enter "TOTAL", in Column A

  • In the same row, enter this formula in Column B =sum(B2:B10)

    • the upper row is the first row in the range. It should include the header row if the header contains text.
    • the lower row is the row containing " Insert new rows above". (In this example, B10 is the row that refers to "Insert new rows above")
  • copy the formula across as many columns as you have data.

Inserting new rows will ALWAYS expand the sum range. You can see the effect in the sample below

insertnewrowsabove


Option#2 - A dynamic formula

  • In the first blank row at the bottom of the column A, enter "TOTAL" in Column A
  • In the same row, enter this formula in Column B =sum(indirect(address(2,column(),4)&":"&address(row()-1,column(),4)))
  • copy the formula across as many columns as you have data.
    • The first row in the SUM range is the header row (Row2)
    • The last row in the SUM range is row()-1; this is the row immediately above the formula, and it is calculated dynamically.
    • The column is created dynamically by column()

indirect

0

In the third scenario shown in your sample spreadsheet, put this formula in cell A27:

=arrayformula( 
  { 
    "Total", 
    sumif( if(row(B20:E26), column(B20:E26)), column(B20:E26), B20:E26 ) 
  } 
)

Then clear row 26. When adding data, insert a row above row 26 to ensure the range references get expanded as your data does.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.