SUMIFS reference another sheet if date = this month

J Smith

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

Paul Newcome

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.

Paul Newcome

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

J Smith

@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

Paul Newcome

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.

J Smith

@Paul Newcome

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