Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Automatically update formula every month
Hi,
I have a sheet listing expenses for every month this year, and expenses for every month last year (Jan 2015, Jan 2016, Feb 2015, Feb 2016 etc).
There is also a column for Total YTD 2015, Total YTD 2016 (if today is December, it would add Jan-Nov 2015 in one column and Jan-Nov 2016 in a column next to it; for comparison).
There are always figures in all 2015 monthly columsn (jan-Dec2015) since we know what the expense were for all last year.
The YTD for 2016 is easy, as the month's we don't have expenses for yet (current or future months) so those will not affect the total.
However, I need the YTD 2015 Total to not capture the current or future months, without having to update this formula every month. This way i can compare apples to apples, Jan-Nov 2015 through Jan-Nov 2016.
How can I do this? Some kind of an If or index formular perhaps?
Thanks!
Comments
-
Hi Olga,
I thought of Sum ifs straight away when I read your Query.
If you identify the columns by year and month separately
Then you can calculate;
the month of reporting limit (Current month -1)
The Sum of the Values in the Row where the Identified year = Somthing (2015) and the months are Less than the reporting limit month.
The boxed cell formula is
=SUMIFS([Jan 2015]1:[Dec 2016]1, [Jan 2015]4:[Dec 2016]4, "2015", [Jan 2015]5:[Dec 2016]5, <=[YTD 2016]6)
Translated = sum row 1 where row 4 = 2015 and row 5 <=11.
Hope this helps
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives