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
Check out the Formula Handbook template!