Jan 19 2020 04:13 PM
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
Jan 20 2020 02:00 AM
Jan 20 2020 03:20 AM
SolutionJan 20 2020 04:02 AM
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.
Jan 20 2020 04:11 AM
Jan 20 2020 04:29 AM
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.
Jan 20 2020 04:38 AM
@Sergei Baklan perhaps, but your example returns a single result
Jan 20 2020 06:13 AM
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?
Jan 20 2020 07:34 AM
Jan 20 2020 10:28 PM
Jan 21 2020 06:45 AM
It's all for the best. Dynamic Arrays/spilling is one of the best Office innovations in a long time.
Jan 21 2020 08:18 AM
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.
Jan 21 2020 08:30 AM
Jan 22 2020 03:25 PM
Thanks for the help and link. They tried SUMIFS with one cell then copy for the whole range, it worked.
Best Regards,
Dz
Jan 23 2020 06:03 AM
@Dzung Vu , glad to know you sorted this out
Jan 23 2020 06:28 AM
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:
Jan 23 2020 06:45 AM
Actually that is also dynamic array
Jan 23 2020 07:04 AM
To be clear, not FILTER, SEQUENCE, RANDARRAY, UNIQUE, etc.
Jan 23 2020 07:23 AM
Feb 28 2020 09:48 AM
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
Jan 20 2020 03:20 AM
Solution