# SUMIFS reference another sheet if date = this month

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

This base formula is for previous month, but it does have one issue with it. Try this instead:

=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()) - 1, YEAR(TODAY()))))

• ✭✭✭✭
edited 01/29/24
Options

@Paul Newcome Thank you for taking a look at this.

I'm needing this formula to calculate this calendar month. The previous month formulas that I have are still working.

Thanks

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

I did confirm that it is infact calculating Dec 2023. So it appears the previous month sections are working. Thank you for the help with this month. That formula fixed it

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!