Can't COUNTIF if NO MATCH?

Dominique Rose Winther

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:

Bassam Khalil

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

Paul Newcome

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.