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

Meredith Krueger

RBD.xlsx

Meredith Krueger

=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.

Meredith Krueger

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

Meredith Krueger

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. ;)

Screen Shot 2021-02-28 at 9.01.23 PM.png

Mark Cronk

Try:

=COUNTIFS(Q:Q, FIND("/", Q@row)<1, Q:Q, ISTEXT(@cell))

Mark

Meredith Krueger

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 Cronk

One more attempt:

=COUNTIF(Q:Q, FIND("/", Q@row)<1))

Mark

Meredith Krueger

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 Cronk

Hi @Meredith Krueger ,

This should work:. =COUNTIF(Q:Q, FIND("/", Q@row)<1))

Mark

Meredith Krueger

=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.