# #invalid value error on a counting from previous year month

Options
✭✭✭

Hi good day everyone. I have a formula that was working fine until now January. The formula what is doing is counting all new items added in a sheet on previous month

=COUNTIFS({What is it?}, "Risk", {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - 1), IFERROR(YEAR(@cell), 0 = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - 1))))))

I am just guessing but can be this for the year change? any suggestion to fix this?

as you see below the current month count is working fine but the previous month counting is giving the error

Tags:

• ✭✭✭✭✭✭
Options

You could calculate your YEAR value by finding the year from TODAY(-31). That will find you the year from last month no matter what day of the current month it is.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

I'm not totally clear on what you're doing here, but it seems like wherever you're calculating a YEAR value for the previous month, using TODAY(-31) in place of TODAY() will get you the correct year, each and every January.

=COUNTIFS({What is it?}, "Risk", {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY(-31)), MONTH(TODAY(), 1) - 1), IFERROR(YEAR(@cell), 0 = YEAR(DATE(YEAR(TODAY(-31)), MONTH(TODAY(), 1) - 1))))))

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

You could calculate your YEAR value by finding the year from TODAY(-31). That will find you the year from last month no matter what day of the current month it is.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭
Options

@Jeff Reisman where should i input the -31 into the formula?

=COUNTIFS({What is it?}, "Risk", {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - 1), IFERROR(YEAR(@cell), 0 = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - 1))))))

• ✭✭✭✭✭✭
Options

I'm not totally clear on what you're doing here, but it seems like wherever you're calculating a YEAR value for the previous month, using TODAY(-31) in place of TODAY() will get you the correct year, each and every January.

=COUNTIFS({What is it?}, "Risk", {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY(-31)), MONTH(TODAY(), 1) - 1), IFERROR(YEAR(@cell), 0 = YEAR(DATE(YEAR(TODAY(-31)), MONTH(TODAY(), 1) - 1))))))

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭
Options

Thanks @Jeff Reisman i fixed the formula to a simple one and add the -31 and it works thanks

=COUNTIFS({What is it?}, "Risk", {Status checkbox}, 0, {Created date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY() - 31), IFERROR(YEAR(@cell), 0) = YEAR(TODAY() - 31)))

I left here this formula as reference for the ones who needs.

• ✭✭✭✭✭✭
Options

Happy I could help!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!