Formula

Options

Hi

I need a formula to count a text column but I need to exclude a couple of texts, my formula seems to a bit clunky

I am getting a #UNPARSEABLE error

=COUNTIF ([10/06/24]3:[10/06/24]548, "<>AL - ANNUAL LEAVE", "<>PH - PUBLIC HOLIDAY", "<>RDO - ROSTERED DAY OFF", "<>WEEKEND", "<>CL - CARERS LEAVE", "<>SL - SICK LEAVE", "<>UL - UNPAID LEAVE")

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    Hello @Susan van Niekerk,

    First, you need to remove your operator (<>) from the quotes. Anything in quotes is going to be considered as a string to evaluate.

    I assume you are truing to count all the values that do not equal any of the various strings in quotes you have. If this is correct, you need to add an OR() function with a statement for each value you want to exclude from the count.

    Try the below

    =COUNTIF ([10/06/24]3:[10/06/24]548, OR(@cell <> "AL - ANNUAL LEAVE", @cell <> "PH - PUBLIC HOLIDAY", @cell <> "RDO - ROSTERED DAY OFF", @cell <> "WEEKEND", @cell <> "CL - CARERS LEAVE", @cell <> "SL - SICK LEAVE", @cell <> "UL - UNPAID LEAVE"))

    Hope that helps!

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Susan van Niekerk
    Options

    Hi Dan

    I get a error #UNPARSEABLE

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!