SUMIFS Help - Calculate a range based on current month

Luke W.

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!

Todd M Keller

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)))))))))))))

Eric Law

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)"

Todd M Keller

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)))))))))))))

Luke W.

Thank you both for your help. @Todd M Keller - That did the trick!