# Dynamic formula to summarize each column of several rows when adding rows

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.

Thank you

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

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

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.