!SPILL error with SUMIFS formula

[Dzung Vu]

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

[#2]

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?

[#3 · Accepted solution]

@Dzung Vu 

It could be similar to https://techcommunity.microsoft.com/t5/excel/spill-error-when-doing-vlookup/m-p/1117822#M49530

[#4]

@Dzung Vu 

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.

[#5]

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.

[#6]

@Jan Karel Pieterse 

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.

[#7]

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

[#8]

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?

[#9]

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.

[#10]

@Sergei Baklan 

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

Cheers

[#11]

@Jan Karel Pieterse 

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

[#12]

@Jan Karel Pieterse 

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.

[#13]

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

[#14]

Hi @Sergei Baklan 

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

Best Regards,

Dz

[#15]

@Dzung Vu , glad to know you sorted this out

[#16]

@Dzung Vu 

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:

[#17]

@Patrick2788 

Actually that is also dynamic array

[#18]

@Sergei Baklan 

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

[#19]

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

[#20]

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