CountIF <> "#NO MATCH"

I'm using the Linked Columns tool and every unmatched result is returning #NO MATCH. This wouldn't be a problem except I have a formula that counts the number of columns with a value and checks this against the expected number of values to determine row status. My previous Count(columnA:columnZ) doesn't work, and I've now discovered that CountIF(columnA:columnZ,<>"#NO MATCH") also doesn't work because #NO MATCH is an error and not a text value.

Is there a way to count the columns with an actual value and ignore columns with the error as if it were a blank?

Find more posts tagged with

Countif

Accepted answers

All comments

jjg279

Hey Jacob, you could wrap your whole formula in an "IFERROR" and tell it to put in whatever you want. It would look something like this:

=IFERROR(COUNTIF(…), "Not found") - you could also just put "" if you wanted it to be blank.

Jacob Minnich

I still need to be able to return a count of cells that have content that is not "# NO MATCH". Wrapping the COUNTIF in an IFERROR will return the error response if any cells in the array are "#NO MATCH". I have mixed values and "#NO MATCH" in the array and need to treat "#NO MATCH" cells as blanks so the COUNTIF will exclude them.

jjg279

Hey @Jacob Minnich , I think I am confused, if you needed the cells to be blank, you should just be able to put empty parathesis in the IFERROR function, that will return a blank cell.

Alternatively, looking at something you tried above you could wrap it in an IFERROR(COUNTIF(), "Not Found") and use

COUNTIF(columnA:columnZ,<>"Not Found")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.