0

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?

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.

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.