What am I missing....

Peter Graham

Some Background:

I have been running these sheets for over 6 months and the formulas have never been a problem. It is a SUMIFS where the criterion is being searched for in a different sheet. I added a new column and wanted to do the same formula but searching in a different sheet.

Column "Funds Spent MASTER DUMP" is returning 0 however see below for report results showing data in "MASTER DUMP" sheet that matches this criteria:

The formula should return the sum of 'Net (Actual)' but isn't.

As I said, this same formula has been successfully run in the column above in first example "Funds Spent Approved" and I have never had problems working through spelling mistakes or coding wrong but I have tried this over 50x and getting no where.

What am I missing

Peter Graham

Thanks David for getting back to me! This drove me crazy for a couple of hours yesterday. Then I come in today, create new column and start again - It works perfectly!!!

David Tutwiler

@Peter Graham Glad to hear it's working. I have seen issues before where cross-reference ranges acted funny from time to time. In that case, I would re-designate the range with a slightly different name (ie. Location_1 instead of Location) and sometimes that would work. If you get stuck again, it might be something to try.

David Tutwiler

The only thing I can see in your screen shot is that your first location is "Developmer" This could be a screencut off that isn't wrapped, but if it is not then that would not match the "Development" you have in the second Location screenshot. Outside of that, everything looks right to me.

Peter Graham

Thanks David for getting back to me! This drove me crazy for a couple of hours yesterday. Then I come in today, create new column and start again - It works perfectly!!!

Genevieve P.

Hi @Peter Graham, I agree with @David Tutwiler

Other than that one difference in spelling (if it is a difference) it looks to be set up correctly.

The next thing to test would be to make sure that your cells really are matching across sheets, that the formula is reading the content the same and finding a proper match. I would swap the SUMIFS formula to be a COUNTIFS (removing the first SUM range) just to ensure that the formula is able to match up each of your criteria.

If you get a 0 with the COUNTIFS then we'll need to check each of the criteria to see where the matching is breaking.

If you get a correct number with the COUNTIFS then we know there's something going on with the SUM range (The [Net (Actual)] column) and can troubleshoot further.

David Tutwiler

@Peter Graham Glad to hear it's working. I have seen issues before where cross-reference ranges acted funny from time to time. In that case, I would re-designate the range with a slightly different name (ie. Location_1 instead of Location) and sometimes that would work. If you get stuck again, it might be something to try.