OFFSET Function

Julie Bryant

Hi,

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

and

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,

Julie

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

Julie Bryant

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

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!

Best,

Heather

Julie Bryant

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,

Julie

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!