SOLVED

# !SPILL error with SUMIFS formula

Occasional Contributor

# !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

# Re: !SPILL error with SUMIFS formula

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?
Best Response confirmed by Dzung Vu (Occasional Contributor)
Solution

# Re: !SPILL error with SUMIFS formula

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.

# Re: !SPILL error with SUMIFS formula

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.

# Re: !SPILL error with SUMIFS formula

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.

# Re: !SPILL error with SUMIFS formula

@Sergei Baklan perhaps, but your example returns a single result

# Re: !SPILL error with SUMIFS formula

Hi Jan,

We work in the BI space and so many reports for so many clients.

The question for most people are is "How can you disable this functionality?"

Are we not given a choice here?

# Re: !SPILL error with SUMIFS formula

No, there is no choice I'm afraid. Go with this, it is a crucial change in how Excel works and opens doors to very nice ways to setting up calculations. But it will take getting used to.

# Re: !SPILL error with SUMIFS formula

Thank you Sergei, I have passed the link to the users, they will let me know.

Cheers

# Re: !SPILL error with SUMIFS formula

It's all for the best.  Dynamic Arrays/spilling is one of the best Office innovations in a long time.

# Re: !SPILL error with SUMIFS formula

When something used to work fine and now doesn't work at all, no, that is not a "crucial" change by any stretch of the imagination.  That's breaking a tool that wasn't broken.  That is punishing some users at the expense of others and without an opt-out.

I have zero need or want of this function.  I need to be able to teach Excel to staff and now the way it works on my computer is radically and inalterably different from how it works on theirs.  With every other feature I've ever used, I could make the newer machine act like the old at least for training or for old documents.  This is a monkey wrench in the spokes for people who use this stuff daily.

# Re: !SPILL error with SUMIFS formula

I'm sorry but I care to disagree. Version differences have always been there to cater for for trainers. Like in the past you need to adapt your setup to your training audience. ALl that has changed is the way how you change your incarnation of Office. See: https://docs.microsoft.com/nl-nl/DeployOffice/overview-of-update-channels-for-office-365-proplus

# Re: !SPILL error with SUMIFS formula

Thanks for the help and link. They tried SUMIFS with one cell then copy for the whole range, it worked.

Best Regards,

Dz

# Re: !SPILL error with SUMIFS formula

@Dzung Vu , glad to know you sorted this out

# Re: !SPILL error with SUMIFS formula

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:

# Re: !SPILL error with SUMIFS formula

Actually that is also dynamic array

# Re: !SPILL error with SUMIFS formula

To be clear, not FILTER, SEQUENCE, RANDARRAY, UNIQUE, etc.

# Re: !SPILL error with SUMIFS formula

It does not take one of the new functions to make a formula spill, any formula which returns more than a single result will spill, regardless of the functions used

# Re: !SPILL error with SUMIFS formula

This "SPILL!" error is garbage, it is causing issues with code that worked fine and now is broken. Microsoft contines to build on reasons to change to Google, my spouse works for a major corporation that just announced they are dumping Microsoft and going to Google. I am starting to think the same thing, with O365 it is just way to easy for Microsoft to crash your data functions with update "improvement". @Jan Karel Pieterse