How to sum specific columns when adding new columns for hundreds of rows?

Question

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?

Answer 1

=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.