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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!