OFFSET Function
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
Answers
-
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
bassam.khalil2009@gmail.com
☑️ 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"
-
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,
-
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
-
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
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!