CountIFS - last month, current month and NOT cancel

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.

Paul Newcome

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

Paul Newcome

Try replacing the second AND with a comma.

Trish Dillon

@Paul Newcome thanks - I get unparseable error

=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")))

Meg Y

Here are a few tricks I've tried.

If possible, break down the formula by parts and see if each one works. Then piece back together.

The next thing I try is opening a word doc and seperate out each function on its own row. This trick would sometimes help me find a missing comma or ().

For all my working formulas, I usually remove the = sign and save the sheet and then add it back. This way the formula is always in the cell history.

Trish Dillon

@Meg Y - thanks that is a good tip.

Meg Y

@Trish Dillon - Let me if one of them works. If you find a different trick please share. Always trying to add to my bag of tricks.

Paul Newcome

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

Meg Y

@Paul Newcome - That explains why I would still get an error when correcting a formula in Word. I'll add that to my tips/tricks sheet. - Meg