#UNPARSEABLE error in sheet summary field formula

For my Customer ID column, I'd like to count how many cells have errors so I'm trying this:

=COUNTIF([Customer ID]:[Customer ID], not(iserror(@row )))

The Customer ID column is a lookup from elsewhere, and typically the error that could show up is #NO MATCH. I've tried a few variations of this formula but can't get it to work.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

I usually wrap the formula outputting the error in an IFERROR then count the number of "ERROR" cells.

=IFERROR(INDEX(…………., MATCH(……., …….., 0)), "ERROR")

=COUNTIFS([Original Column]:[Original Column], @cell = "ERROR")

Going the helper column route as suggested by @nicksnav, instead of using the COUNTIF, I would just use a SUM.

=SUM([Error Helper]:[Error Helper])

All comments

nicksnav

Hey @akamholtz

From what Iv'e found you can directly count error messages in Smartsheet. I have not been able to get that to work. However I am able to with a helper column to convert an error message into a countable character.

[Helper Customer ID]: =IF(ISERROR([Customer ID]@row ), 1, "") - This will turn an error message into a 1 or do nothing.

[Customer ID Error Count]: =COUNTIF(Department:Department, 1) - This will count all the 1s you've created in your helper column.

There may be a better solution to this, but this is a way to do it that is tested and works.

Paul Newcome

I usually wrap the formula outputting the error in an IFERROR then count the number of "ERROR" cells.

=IFERROR(INDEX(…………., MATCH(……., …….., 0)), "ERROR")

=COUNTIFS([Original Column]:[Original Column], @cell = "ERROR")

Going the helper column route as suggested by @nicksnav, instead of using the COUNTIF, I would just use a SUM.

=SUM([Error Helper]:[Error Helper])

akamholtz

Thanks for this. I didn't want to create a helper column, and since it seems like there's no way directly count the errors, adding the wrapper to the initial formula feels like the best way to go.

(Really seems like it should be easier to count 'system' errors…)

[Deleted User]

Your formula isn’t working because COUNTIF can’t evaluate ISERROR() using @row@row . You need to check errors per cell.

Use this formula instead:

=COUNTIF([Customer ID]:[Customer ID], HASERROR(@cell ))

This will count all cells in Customer ID that contain errors (including #NO MATCH).

If that doesn’t work, use a helper column:

Helper column formula:

=IF(ISERROR([Customer ID]@row ), 1, 0)

Count errors:

=SUM([Helper Column]:[Helper Column])

Paul Newcome

Happy to help. 👍️

Paul Newcome

@AmarK Please check your source(s) before posting. Smartsheet does not have a HASERROR function.

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.