Google Sheets – How to Sum Specific Columns When Adding New Columns

google sheetsworksheet-function

I've got a sheet in Google Sheets that tracks a large inventory. I want to quickly see the total of the last three months (columns) but ONLY the last three months. So far it looks something like this: 

           3 MO TOTALS    APRIL    MARCH     FEB      JAN

APPLES          10          2         4        4       4   

BANANAS          5          0         2        3       2 

CARROTS          0          0         0        0       1

The issue comes when it's time for a new column to be added. If I use =SUM, the formula changes from =SUM(C2:E2) to =SUM(D2:F2). =SUM($C2:$E2) also does not work.

If I use =sum(indirect("C2:E2")), I cannot copy the formula easily and I have over 300 rows of entries and add new ones frequently.

How do I keep absolute column ranges without writing out each row's formula manually?

Best Answer

=sum(INDEX(B2:F2, 1, 2):INDEX(B2:F2, 1, 4)) to be entered in cell B2.

  • Since the "INDEX" function includes B2, the range will expand to include any new columns inserted to the right of B2.
  • "INDEX" is used to give a starting cell and an ending cell for the "SUM" function.
    • The starting position is the cell immediately to the right of cell B2
    • The ending position is the cell three cells to the right of cell B2.
    • This creates a range that will consistently represent "the last three months" no matter how many columns are inserted to the right of Column B.
Related Topic