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

  • Todd M Keller
    Todd M Keller ✭✭✭✭
    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

  • Eric Law
    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
    Todd M Keller ✭✭✭✭
    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)))))))))))))

  • Luke W.
    Luke W. ✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!