SUMIFS reference another sheet if date = this month

This formula worked until the new year. Any tweak I have tried breaks the formula all together. Currently the formula returns $0. There is data on source sheet it should be calculating but its not recognizing "This month AND This year"

=SUMIFS({INV TTL}, {INV Auth Date}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY())), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()), YEAR(TODAY()))))

Any pointers would be appreciated. Thanks

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Current calendar month would be:

    =SUMIFS({INV TTL}, {INV Auth Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    Have you confirmed previous month is in fact calculating correctly? The issue is in the bold portion:

    =SUMIFS({INV TTL}, {INV Auth Date}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY())), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()), YEAR(TODAY()))))


    Regardless of which month we are in, the formula is calculating for the current year. So in January 2024, it is calculating for December of 2024 and NOT December of 2023 like you would expect from a "previous month" type of formula.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!