Excel Form. works well but not in Smartsheet =COUNTIFS(Q:Q,"<>*/*/*",Q:Q,"<>",Q:Q,"<>Complete")"

Best Answer

  • edited 03/01/21 Accepted Answer

    =COUNTIFS(Prep:Prep, FIND("/", @cell) > 1)

    This one worked! After renaming the "Q" to my column name "Prep" it still wouldn't work until I renamed the column to something different, renamed the formula to match and then reverted the column name to the orig (which adjusted automatically in the formula. x


    I appreciate you working with me on this and helping me find a solution! Thank you.

Answers

  • I have changed the "Q:Q" to the corresponding Smart Sheet columns. No luck. The "<>*/*/*" is the primary concern. I need to count all cells with this date format (ie 02/24/2021).

  • Mark CronkMark Cronk ✭✭✭✭✭

    Does this get you the response you expect?:

    =COUNTIF(Q:Q, ISDATE(@cell))

    To look for a format you'll need to deconstruct the date. Can be done but not simple.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thank you for the reply Mark! This formula did not work either. I think part of the issue is that my "date" is in number format and not true date format. However, the formula (in my original question) does work in Excel. See screenshot for the column data. I want to find the total cells that do NOT include the numbers. The result should be "8". Any ideas? I would be forever grateful. ;)


  • Mark CronkMark Cronk ✭✭✭✭✭

    Try:

    =COUNTIFS(Q:Q, FIND("/", [email protected])<1, Q:Q, ISTEXT(@cell))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Tried that one. At least is isn't giving me an error. Result is "0". Hmmm, pretty smart to use the "/" to search. I am thinking we are close. Thanks!

  • Mark CronkMark Cronk ✭✭✭✭✭
    edited 03/01/21

    One more attempt:

    =COUNTIF(Q:Q, FIND("/", [email protected])<1))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Result is "#UNPARSEABLE". :( Thanks so much for trying! This was a simple thing to do in Excel - had hoped it would be in Smartsheet as well.

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Meredith Krueger ,

    This should work:. =COUNTIF(Q:Q, FIND("/", [email protected])<1))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • edited 03/01/21 Accepted Answer

    =COUNTIFS(Prep:Prep, FIND("/", @cell) > 1)

    This one worked! After renaming the "Q" to my column name "Prep" it still wouldn't work until I renamed the column to something different, renamed the formula to match and then reverted the column name to the orig (which adjusted automatically in the formula. x


    I appreciate you working with me on this and helping me find a solution! Thank you.

Sign In or Register to comment.