Auto sum for YTD
Hi Team
Can you please help with a formulae wherein on 1st of each month, it can sum on the values for all the previous months. I have the the exact format and month type in the sheet like in the snippet below. I do have 2 helper columns and a Automation to update Today's date and subsequent month based on number.
Can you please suggest a formulae so that on 4/1 it calculates 120 and on 5/1 it calculates 200
Answers
-
A SUMIF function will do this if you can change the month column to either
- a date column containing the 1st of the month
- the numeric reference for the month (1 instead of Jan)
Making either of these changes will allow you to SUM IF either
- the date is before the date you are looking for
- the month number is less than the month number you are looking at
-
Thank you. Can you also help with the SUMIF formulae string so I can make that suggested change and test it.
-
Can you tell me what you changed your month to? A date, or a number?
-
Thanks. I change it to number 1 through 12.
-
If your months are in a column called .Month.. , your numbers to sum are in a column called Sum, and your dates are in a column called On, this formula will sum the values in the On column if the number in the .Month.. column on the row is less than the month of the date in the On column.
=SUMIF([.Month..]:[.Month..], <MONTH(On@row), Sum:Sum)
It looks like this:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!