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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try replacing the second AND with a comma.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Trish Dillon
    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
    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.

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Trish Dillon
    Trish Dillon ✭✭✭✭✭✭

    @Meg Y - thanks that is a good tip.

  • Meg Y
    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.

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Meg Y
    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

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!