Forum Discussion
!SPILL error with SUMIFS formula
Hi All,
we have 2 users who are using Office 365 version, they're both experiencing the issue with SPILL error with formula SUMIFS. I clicked on the the yellow exclamation mark next to the error (cell) it says: The Spill range is too big.
I use the old version (2013) on the same file, I have no issues at all.
Any help/ advice would be much appreciated.
Cheers
27 Replies
That's the imitation of the case
Exactly the same formula. Pre-DA Excel returns 24 (since takes only first element of the array), DA Excel returns spill or #SPILL! error if it's not enough space for the spill.
- This only happens when using multi-cell references in arguments of functions which normally expect a single value. IMO this is a bug with DA Excel when it opens files created in non-DA Excel, it should have inserted the @ operator in front of the cell arguments where a single value is expected.
I guess the question is how to interpret "normally expected". For example, if complicate the formula a bit
=SUM(SUMIFS(B:B,A:A,{"a";"b"}))it shall not be converted with implicit intersection sign.
Hi SergeiBaklan
Thanks for the help and link. They tried SUMIFS with one cell then copy for the whole range, it worked.
Best Regards,
Dz
For those interested. It doesn't take much to produce a SPILL error. You don't need to use a dynamic array to get the error.
It could be something as simple as this:
- Can you please post a small sample spreadsheet demonstrating the error? Those two users have just received the update for Office in which Excel now works significantly differently when it comes to formulas which may return more than one result. I'm surprised it yields a different result for them. If a sample spreadsheet is not possible, can you please upload a screen-shot which shows both the problem and the formula in the problem cell?
