Can't COUNTIF if NO MATCH?

Options

So strange - the formula appears to be right, but something isn't working...

The formula looks across 18 columns to see if there are any that have the text "SrLead3" - an identifier for a job title. That identifier is in that row, but if #NO MATCH appears in any of the cells it is looking in for the COUNTIF formula, then I don't get the count result - I also get #NO MATCH. But the text is there, so I'm not sure what's happening.

The formula:

=IF(ISBLANK([#1 Activity Description]@row), "", COUNTIF([Contact 1]@row:[Job Title 14]@row, CONTAINS("SrLead3", @cell)))

The cell with the formula:

The cells it is looking in:

You can clearly see that in the cells it is searching in, it should find SrLead (its in the Job Title 4 column). Instead, it returns #NO MATCH:


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Dominique WINTHER

    Hope you are fine, could you please add a copy of your sheets (after removing or replacing any sensitive information). in excel format and i will write the exact formula for you, please send it to my Email

    bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 04/07/21
    Options

    The issue is that the #NO MATCH error pulls through the rest of the other functions. You would have the same result using a SUMIFS or any other function that covers the same range.


    You can wrap the formula that is producing the initial error in an IFERROR to output a blank or to even output a text string of "#NO MATCH" so that the error itself is gone from the range but it still displays that particular text string.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!