Can't COUNTIF if NO MATCH?

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:

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.