Write auto sum (n+2)-th column in n-th column

Question

I've been struggling for a few weeks to make an array formula which could automatically write in n-th column (of row 3) the sum of (n+2)-th column.

Here is my spreadsheet. https://docs.google.com/spreadsheets/d/12FY1n9QHl-79WgxEu5ZFLJPfZejWOsM0SdN7OJcwwrA/edit?usp=sharing

In my spreadsheet, I want the formula to write the sum of d5 to d in b3, the sum of h5 to h in f3, etc automatically. Is it possible?

I just write a formula but it only write the sum of column D in every (2 mod(4))-th column of row 3. Here is my formula. =ARRAYFORMULA(IF(MOD(COLUMN(D3:3)-COLUMN(D3), 4) = 0, SUM(D:D),""))) — Yanuz Nurchalik

Tom Sharpe Tom Sharpe · Accepted Answer · 2018-10-22T15:35:08

I think because of the merged cells that you don't have to check whether the column is a multiple of 4. You can just use the formula for column totals:

=ArrayFormula(mmult(transpose(row(D5:D))^0,n(D5:DP)))

Write auto sum (n+2)-th column in n-th column

1 Answers