SUMIFS #REF! error message

Question

Level 1

93 points

SUMIFS #REF! error message

I have had a spreadsheet for years where I have one table reference another table of values and test with the SUMIFS function. It worked fine until recently when now I get "The formula contains and Invalid reference" message. Nothing otherwise has changed with the tables. I have tried deleting the formula and re-entering only to get constant REF error messages.

Is this a new, common bug and is there a fix/workaround or do I wait for an update of Numbers?

MacBook Pro with Retina display, iOS 9.1, iPhone6, iPadAir, Watch

Posted on Aug 10, 2016 11:21 AM

Reply

Answer 1

Barry

Level 9

60,344 points

Aug 10, 2016 11:51 AM in response to Flyingdp

Without the benefit of a perch behind you where we can see the screen and the tables involved, there's not enough information to do anything beyond hazarding a guess.

Most REF errors I've seen result from a reference to a cell or cells beyond the edge of a table—eg. a reference to B16 in a table with two columns and 14 rows.

What is the formula? Is there a copy of the formula on the table that does not throw this error?

Screen shots of the table(s) involved, showing the row and table reference tabs, and the table name(s) could also be useful.

Regards,

Barry

Reply

Answer 2

Flyingdp Author

Level 1

93 points

Aug 10, 2016 12:59 PM in response to Barry

Thanks Barry. I appreciate you replying to my query.

But as mentioned, I have changed nothing from the previously working spreadsheet. So there is no question of referencing cells that don't exist because (a) they do exist and (b) the references and formulae were working up until now. I even deleted the original formula and re-entered all including references just to be sure.

There is no copy of the formula that no longer throws the error. Several versions (testing different column names) of the same formula all worked.

IMHO the issue seems to have started after a Pages/Numbers update, and only seems to affect references to tables outside of the one doing the testing.

Here is a copy of the original I grabbed from TimeCapsule

IF(SUMIFS(Inv TOTAL,INV SENT,TRUE,Client,A6)=0,"",SUMIFS(Inv TOTAL,INV SENT,TRUE,Client,A6))

Now it looks like

IF(SUMIFS(#REF!,#REF!,TRUE,#REF!,A6)=0,"",SUMIFS(#REF!,#REF!,TRUE,#REF!,A6))

Reply

Answer 3

t quinn

Level 5

7,063 points

Aug 10, 2016 5:40 PM in response to Flyingdp

Hi Flyingdp,

This is not a bug in the program. Something happened to break those references. A new version of Numbers is not going to change things.

Even without knowing how or why they broke you can fix this.

Starting with your broken formula, click on the first #REF!. Navigate to the values you want summed and click that column. Click on the second #REF! and navigate to your first set of test values click the column. Repeat for the rest of your test values.

FYI- You could simplify your formula to SUMIFS(Inv TOTAL,INV SENT,TRUE,Client,A6) and use conditional highlighting to whiteout a 0 result.

quinn

Reply

Answer 4

Flyingdp Author

Level 1

93 points

Aug 12, 2016 5:58 AM in response to t quinn

Thanks Quinn.

I have an interesting resolution to the problem: after closing the file, shutting down/restarting Numbers, and opening the offending file, I once again had warning triangles in the cells containing the formula. However, this time, when clicking on a triangle (to normally get a dialogue box explains the error) the cell immediately displayed the correct result of the formula with no more Ref issues. There were several cells with the same formula and clicking on each warning triangle negated the error for that cell and displayed the correct result.

I didn't change or do anything else to the spreadsheet -- go figure.

Also, thanks for the tip on simplifying the test formula! Certainly a more efficient way of performing the test - I never thought about using conditional highlighting this way.

Rob

Reply