OFFSET Function


I need to calculate the sum across a number of columns that will change every month (mon columns are set from January to December for each year). This would easily be done in Excel via an OFFSET function and I can't find what the equivalent would be in Smartsheet.

What I am trying to get at is this:

=SUM(OFFSET([Current Month]@row,0,0,1,[Jan'21 LE / Act]@row))

Where Current Month column shows the number of the month we are in


Jan'21 LE/Act column is the first column I am summing data from for 2021.

Can someone advise how I could get to my result?

Many thanks,



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Julie Bryant

    Hope you are fine, you can do that using the SUMIFS function in smartsheet, if you can supply a screenshot for your sheet ( remove any sensitive data ) i can write the exact formula for you

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi @Bassam.M Khalil

    Thanks for offering to help and here is a screenshot of what I am trying to achieve.

    I need the column "YTD 2021 Act" to calculate the growing sum of the actuals depending on what month we are in.

    When current month is February (2), it will sum up Jan'21 and Feb'21 LE/Act data, when in April (Month 4), it should automatically increase the range to calculate Jan'21 to Apr'21 Le/Act data.

    Hope this helps,

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Julie Bryant ,

    Try this:

    =IF(MONTH(TODAY()) = 1, [jan’21 LE / act]@row, IF(MONTH(TODAY()) = 2, SUM([jan’21 LE / act]:[feb’21 LE / act]), IF(MONTH(TODAY()) = 3, SUM([jan’21 LE / act]:[mar’21 LE / act]), IF(MONTH(TODAY()) = 4, SUM([jan’21 LE / act]:[apr’21 LE / act]), IF(MONTH(TODAY()) = 5, SUM([jan’21 LE / act]:[may’21 LE / act]), IF(MONTH(TODAY()) = 6, SUM([jan’21 LE / act]:[jun’21 LE / act]), IF(MONTH(TODAY()) = 7, SUM([jan’21 LE / act]:[jul’21 LE / act]), IF(MONTH(TODAY()) = 8, SUM([jan’21 LE / act]:[aug’21 LE / act]), IF(MONTH(TODAY()) = 9, SUM([jan’21 LE / act]:[sep’21 LE / act]), IF(MONTH(TODAY()) = 10, SUM([jan’21 LE / act]:[oct’21 LE / act]), IF(MONTH(TODAY()) = 11, SUM([jan’21 LE / act]:[nov’21 LE / act]), IF(MONTH(TODAY()) = 12, SUM([jan’21 LE / act]:[dec’21 LE / act]), ""))))))))))))

    It basically translates to "if today's date is in the first month, display the January total. If today's date is in the second month, sum January through February. If today's date is in the third month, sum January through March....." and so on.

    Hope this helps!



  • Thanks Heather @Heather D ! I added @row after each column reference ([jan’21 LE / act]@row:[feb’21 LE / act]@row) to make sure it was only summing what was in the row and not the entire column and it works.

    It's not lean, and I certainly miss the OFFSET function but it does the trick for now 😊

    Much appreciated,


  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    @Julie Bryant Ah, perfect! Glad it worked!

    I agree - there are some things that Excel does much more cleanly; there are other things that Smartsheet does more cleanly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!