Sum up values with #NO MATCH error

HI All,

Some of the cells that I want to sum up are a "#NO MATCH" which is fine as those values are based on other formulas and there might be no value for them.

Now, trying =SUM(IFERROR(Cellsrange,0)) is not working, its giving me "0" as a result which is incorrect.- see attached.

Please help as its really painful doing this manually, knowing that those values might change..

Thanks,

Mia

sumerror.PNG

Find more posts tagged with

Formulas

Accepted answers

All comments

Peggy Parchert

Hello @Smartsheet newbie MM

Would this work?

=IFERROR(SUM([Jun-23 \[h\]]429:[Jun-23 \[h\]]431), 0)

Peggy

Smartsheet newbie MM

Hi @Peggy Parchert, no it ended up in the same result- "0". See attached.

sumerror1.PNG

Peggy Parchert

@Smartsheet newbie MM

Why do you have a #NO MATCH? Is that a formula?

Peggy

Smartsheet newbie MM

https://community.smartsheet.com/discussion/comment/405816#Comment_405816

@Peggy Parchert yes its a formula that gets the values in if available. It's a month on month trend so some months have a value and others don't and the months that don't have come back as NO MATCH.

I am just wondering because the =sum(iferror.. works in excel..and i would have expected this to work in smartsheet as well, I don't think its so uncommon to happen.

Peggy Parchert

@Smartsheet newbie MM

Apologize for the delay. I would recommend that you add an IFERROR to the formula that is showing #NO MATCH in your screenshots making it show as "No Match". Reasoning is the #NO MATCH is set by SS and we do not know what value they are using to set it.

If you update the #NO MATCH formula, I believe =IFERROR(SUM([Jun-23 \[h\]]429:[Jun-23 \[h\]]431), 0) should work.

Peggy

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.