SUMIF not working with text strings pulled in via Index/Match

I am looking to calculate a running total value for the amount of financial assistance we provide for some of the moms that we help at our organization.

I am currently pulling information over from our "Mom Master List" to another sheet via Index/Match, and then adding additional information in other columns.

When I try to do a sheet summary of our running totals per month (summing all financial assistance provided each month), it gives me a blank value - or "NO MATCH" if I didn't have the IFERROR(). Below you will find the sheet summary formula that I used, the information column that I pulled in via Index/Match ("Mom Type 2"), and the column that I would like to sum ("May (2022)")

Any and all help / advice would be much appreciated! I could also potentially avoid this issue if I filtered the moms before index/matching them over to the new sheet, so if you have advice on that, that would be really appreciated as well!

Screen Shot 2022-08-04 at 3.48.41 PM.png

Screen Shot 2022-08-04 at 3.48.29 PM.png

Screen Shot 2022-08-04 at 3.48.16 PM.png

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

How exactly are you populating the May 2022 column?

Oliver Berning

@Paul Newcome

It's just a manually added text / number field with only numbers (so the SUMIF function works properly).

Paul Newcome

But you were saying you get a "NO MATCH" without the IFERROR statement?

Oliver Berning

https://community.smartsheet.com/discussion/comment/338814#Comment_338814

That's correct

Paul Newcome

Ok. So is the SUMIF function working properly or no?

Oliver Berning

https://community.smartsheet.com/discussion/comment/338844#Comment_338844

I'm not getting any values out, so no. Could be another issue, but I can't think of anything, that's why I reached out.

Kelly Moore

Hey @Oliver Berning

As a test, please try

=SUM([May (2022)]:[May (2022)])

Does that give you a value? If it does not, the problem is with this column.

In your current data set, what is the formula for the [Mom Type 2]? Is it giving you the NoMatch errors?

Also try this. It might weed out any rows that have errors associated with them.

SUMIFS([May (2022)]:[May (2022)],[May (2022)]:[May (2022)],ISNUMBER(@cell), [Mom Type 2]:[Mom Type 2], OR(@cell = "Active financial assistance", @cell= "Financial assistance pending"))

Kelly

Oliver Berning

https://community.smartsheet.com/discussion/comment/338979#Comment_338979

Hey @Kelly Moore!

The summation gives a correct value for all rows in the document, but I only want to sum all of the rows that are visible (aka the filtered moms that are "Active Financial Assistance" or "Pending Financial Assistance"). We have moms that we've already helped that are marked as "Financial Assistance Complete" but I don't want to add these numbers to the running total.

The formula for the [Mom Type 2] column is a simple index / match, pulling a text/number field from a "master list." The column type for [Mom Type 2] in this sheet is also a text/number field.

I will look into removing any potential errors by weeding them out using the formula provided, but briefly scrolling through all of the rows it looks like all of the values are numerical. I'll try it nonetheless and see what happens!

Thank you for your help! :D

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.