SUMIFS Help - Calculate a range based on current month
Hello!
I'm looking for a formula that I can use to calculate a range based on current month.
Attached is a screenshot of what I'm working with.
I want the YTD column to provide a total sum based on what month it is currently. Example - We are currently in march, so YTD would show the sum of Jan, Feb, and March so far.
When we move into April, it will also add that to the total.
I hope that makes sense for what I'm trying to do.
Any help would be greatly appreciated!
Thanks!
Best Answer
-
This assumes that your data is in row "1", and your Month columns are labelled the same way as they are in your example....
=IF(MONTH(TODAY()) = 1, Jan1, IF(MONTH(TODAY()) = 2, SUM(Jan1:Feb1), IF(MONTH(TODAY()) = 3, SUM(Jan1:Mar1), IF(MONTH(TODAY()) = 4, SUM(Jan1:Apr1), IF(MONTH(TODAY()) = 5, SUM(Jan1:May1), IF(MONTH(TODAY()) = 6, SUM(Jan1:Jun1), IF(MONTH(TODAY()) = 7, SUM(Jan1:Jul1), IF(MONTH(TODAY()) = 8, SUM(Jan1:Aug1), IF(MONTH(TODAY()) = 9, SUM(Jan1:Sep1), IF(MONTH(TODAY()) = 10, SUM(Jan1:Oct1), IF(MONTH(TODAY()) = 11, SUM(Jan1:Nov1), IF(MONTH(TODAY()) = 12, SUM(Jan1:Dec1)))))))))))))
Answers
-
Hello @Luke W
I think you need to change where things are a bit, and you can do the YTD as a summary or in a different cell, up to you. Here is what I would do and the formula used "=SUMIF(MonthN:MonthN, <=MONTH(TODAY()), ValueV:ValueV)"
-
This assumes that your data is in row "1", and your Month columns are labelled the same way as they are in your example....
=IF(MONTH(TODAY()) = 1, Jan1, IF(MONTH(TODAY()) = 2, SUM(Jan1:Feb1), IF(MONTH(TODAY()) = 3, SUM(Jan1:Mar1), IF(MONTH(TODAY()) = 4, SUM(Jan1:Apr1), IF(MONTH(TODAY()) = 5, SUM(Jan1:May1), IF(MONTH(TODAY()) = 6, SUM(Jan1:Jun1), IF(MONTH(TODAY()) = 7, SUM(Jan1:Jul1), IF(MONTH(TODAY()) = 8, SUM(Jan1:Aug1), IF(MONTH(TODAY()) = 9, SUM(Jan1:Sep1), IF(MONTH(TODAY()) = 10, SUM(Jan1:Oct1), IF(MONTH(TODAY()) = 11, SUM(Jan1:Nov1), IF(MONTH(TODAY()) = 12, SUM(Jan1:Dec1)))))))))))))
-
Thank you both for your help. @Todd M Keller - That did the trick!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!