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!
Answers
-
How exactly are you populating the May 2022 column?
-
It's just a manually added text / number field with only numbers (so the SUMIF function works properly).
-
But you were saying you get a "NO MATCH" without the IFERROR statement?
-
That's correct
-
Ok. So is the SUMIF function working properly or no?
-
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.
-
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
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!