CountIFS - last month, current month and NOT cancel

Trish Dillon
Trish Dillon ✭✭✭✭✭✭

What am I doing wrong?

This is my metrics sheet and I want to count if - last month has a date in the completed column and does NOT has a cancel status.

I will need one for each last month and the current month.

Best Answer

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

    Breaking the formula out is a great tip, but I would suggest using a text editor such as Notepad. Programs like Microsoft Word use smart quotes which are going to look slanted or have some other kind of indicator of open vs closed. Smartsheet does not recognize these as valid characters.

    "These Quotes" that are straight up and down are the only quotes recognized by Smartsheet.

    If breaking down your formula exposes a missing quote and you add it in using Word, you will still get an error because it is the wrong type of quote.

    As for the formula... It looks like you may need to get rid of an extra set of parenthesis. Try this:

    =COUNTIFS({FY 23 closed}, 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)), {FYStatus}, NOT(@cell = "cancel"))