COUNTIFS formula not functioning how I expect

PinnCason
PinnCason
edited 03/06/24 in Formulas and Functions

Hello,


I am expecting this formula to count the number of cells in the "FND Prepare" column that have a certain name in them, where the cell on the same row in "Prepare Status" is not green.


=COUNTIFS([FND Prepare]:[FND Prepare], HAS(@cell, "NAME"), [Prepared Status]:[Prepared Status], (@cell <> "Green"))


Does someone have an idea as to where I could be going wrong? It is returning a number - 3 - but I am counting at least a dozen cells that have her name in it where the prepare status is not green.


Thank you for any help!

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @PinnCason


    It could be due to the blank values in Prepared Status. Try this formula.

    =COUNTIFS([FND Prepare]:[FND Prepare], "Neva Pence", [Prepared Status]:[Prepared Status], OR(@cell <> "Green", @cell = ""))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @PinnCason


    When you write HAS in your formula, Smartsheet looks for an exact match. If NAME appears as part of another text, like First Name, Smartsheet will not pick this up. Switch the formula as below.


    =COUNTIFS([FND Prepare]:[FND Prepare], CONTAINS("NAME", @cell), [Prepared Status]:[Prepared Status], (@cell <> "Green"))


    OR


    =COUNTIFS([FND Prepare]:[FND Prepare], CONTAINS("NAME", @cell), [Prepared Status]:[Prepared Status], <> "Green")

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Hey @AravindGP,


    Thank you for your help. Unfortunately both formulas provided are returning an incorrect number - now 0. Please see screenshot attached for context. Is there anything else that could be going wrong here?



  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @PinnCason


    It looks like the column is a single select Contact List column. Since there could be only one value, you can try this formula to make it work.


    =COUNTIFS([FND Prepare]:[FND Prepare], "Neva Pence", [Prepared Status]:[Prepared Status], <>"Green")

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • @AravindGP, the new formula is still returning a value of three, which per the screenshot is not accurate. You are correct that the name column is single select. Choosing "restrict column to list values only" does not solve my issue either.


    Could the problem be somewhere in the way Smartsheet is interpreting the names that are input in that column?


    Thank you,

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @PinnCason


    It could be due to the blank values in Prepared Status. Try this formula.

    =COUNTIFS([FND Prepare]:[FND Prepare], "Neva Pence", [Prepared Status]:[Prepared Status], OR(@cell <> "Green", @cell = ""))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Hey @AravindGP,


    Looks like this is the correct solution. the formula was indeed not counting empty cells.


    Thank you so much for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!