SUMIF - looking forward a few months returning a zero after year end

I have this formula to look forward a few months a but once we get into the next year (2022) the result is 0. I've tried several ways but get the same 0 result. What am I missing. Thanks in advance!

=SUMIFS([Man Days]:[Man Days], [Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) + 2), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))) * 8

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Not sure what is going on without more contexts. The criterion1 portion of that formula might have some issues. Try using the DATE formula in the criterion1. The formula below looks at the Start Date column and counts everything in the Man Days column that is greater than 30 days from todays date. Hopefully you can alter this to fit your needs.


    =SUMIFS([Man Days]:[Man Days], [Start Date]:[Start Date], DATE(YEAR([Start Date]@row), MONTH([Start Date]@row), DAY([Start Date]@row)) > TODAY(30))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!