SOLVED
Home

!SPILL error with SUMIFS formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1117288%22%20slang%3D%22en-US%22%3E!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117288%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3Ewe%20have%202%20users%20who%20are%20using%20Office%20365%20version%2C%20they're%20both%20experiencing%20the%20issue%20with%20SPILL%20error%20with%20formula%20SUMIFS.%20I%20clicked%20on%20the%20the%20yellow%20exclamation%20mark%20next%20to%20the%20error%20(cell)%20it%20says%3A%20The%20Spill%20range%20is%20too%20big.%3C%2FP%3E%3CP%3EI%20use%20the%20old%20version%20(2013)%20on%20the%20same%20file%2C%20I%20have%20no%20issues%20at%20all.%3C%2FP%3E%3CP%3EAny%20help%2F%20advice%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1117288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117780%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117780%22%20slang%3D%22en-US%22%3ECan%20you%20please%20post%20a%20small%20sample%20spreadsheet%20demonstrating%20the%20error%3F%20Those%20two%20users%20have%20just%20received%20the%20update%20for%20Office%20in%20which%20Excel%20now%20works%20significantly%20differently%20when%20it%20comes%20to%20formulas%20which%20may%20return%20more%20than%20one%20result.%20I'm%20surprised%20it%20yields%20a%20different%20result%20for%20them.%20If%20a%20sample%20spreadsheet%20is%20not%20possible%2C%20can%20you%20please%20upload%20a%20screen-shot%20which%20shows%20both%20the%20problem%20and%20the%20formula%20in%20the%20problem%20cell%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117920%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117920%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F212301%22%20target%3D%22_blank%22%3E%40Dzung%20Vu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%20similar%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fspill-error-when-doing-vlookup%2Fm-p%2F1117822%23M49530%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fspill-error-when-doing-vlookup%2Fm-p%2F1117822%23M49530%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117933%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117933%22%20slang%3D%22en-US%22%3EJust%20to%20add.%20The%20Spill%20error%20is%20an%20indication%20that%20you%20have%20Dynamic%20Array%20functions%20such%20as%20SORT%2C%20SORTBY%2C%20UNIQUE%2C%20FILTER%20etc...%20%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20check%20your%20SUMIFS%20formula%20to%20ensure%20that%20you%20properly%20provided%20the%20cell%20references%20for%20your%20sum_range%2C%20criteria_range1%2C%20criteria1%2C...%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20all%20is%20fine%2C%20you%20definitely%20won't%20get%20the%20!Spill%20error%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117961%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F212301%22%20target%3D%22_blank%22%3E%40Dzung%20Vu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20the%20imitation%20of%20the%20case%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20413px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166133iB54F47D59C672316%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EExactly%20the%20same%20formula.%20Pre-DA%20Excel%20returns%2024%20(since%20takes%20only%20first%20element%20of%20the%20array)%2C%20DA%20Excel%20returns%20spill%20or%20%23SPILL!%20error%20if%20it's%20not%20enough%20space%20for%20the%20spill.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117968%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117968%22%20slang%3D%22en-US%22%3EThis%20only%20happens%20when%20using%20multi-cell%20references%20in%20arguments%20of%20functions%20which%20normally%20expect%20a%20single%20value.%20IMO%20this%20is%20a%20bug%20with%20DA%20Excel%20when%20it%20opens%20files%20created%20in%20non-DA%20Excel%2C%20it%20should%20have%20inserted%20the%20%40%20operator%20in%20front%20of%20the%20cell%20arguments%20where%20a%20single%20value%20is%20expected.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117992%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20the%20question%20is%20how%20to%20interpret%20%22normally%20expected%22.%20For%20example%2C%20if%20complicate%20the%20formula%20a%20bit%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUM(SUMIFS(B%3AB%2CA%3AA%2C%7B%22a%22%3B%22b%22%7D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3Bit%20shall%20not%20be%20converted%20with%20implicit%20intersection%20sign.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1118006%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1118006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20perhaps%2C%20but%20your%20example%20returns%20a%20single%20result%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1118143%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1118143%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20work%20in%20the%20BI%20space%20and%20so%20many%20reports%20for%20so%20many%20clients.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20question%20for%20most%20people%20are%20is%20%22How%20can%20you%20disable%20this%20functionality%3F%22%3C%2FP%3E%3CP%3EAre%20we%20not%20given%20a%20choice%20here%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1118270%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1118270%22%20slang%3D%22en-US%22%3ENo%2C%20there%20is%20no%20choice%20I'm%20afraid.%20Go%20with%20this%2C%20it%20is%20a%20crucial%20change%20in%20how%20Excel%20works%20and%20opens%20doors%20to%20very%20nice%20ways%20to%20setting%20up%20calculations.%20But%20it%20will%20take%20getting%20used%20to.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1119196%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1119196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Sergei%2C%20I%20have%20passed%20the%20link%20to%20the%20users%2C%20they%20will%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120088%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20all%20for%20the%20best.%26nbsp%3B%20Dynamic%20Arrays%2Fspilling%20is%20one%20of%20the%20best%20Office%20innovations%20in%20a%20long%20time.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120353%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20something%20used%20to%20work%20fine%20and%20now%20doesn't%20work%20at%20all%2C%20no%2C%20that%20is%20not%20a%20%22crucial%22%20change%20by%20any%20stretch%20of%20the%20imagination.%26nbsp%3B%20That's%20breaking%20a%20tool%20that%20wasn't%20broken.%26nbsp%3B%20That%20is%20punishing%20some%20users%20at%20the%20expense%20of%20others%20and%20without%20an%20opt-out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20zero%20need%20or%20want%20of%20this%20function.%26nbsp%3B%20I%20need%20to%20be%20able%20to%20teach%20Excel%20to%20staff%20and%20now%20the%20way%20it%20works%20on%20my%20computer%20is%20radically%20and%20inalterably%20different%20from%20how%20it%20works%20on%20theirs.%26nbsp%3B%20With%20every%20other%20feature%20I've%20ever%20used%2C%20I%20could%20make%20the%20newer%20machine%20act%20like%20the%20old%20at%20least%20for%20training%20or%20for%20old%20documents.%26nbsp%3B%20This%20is%20a%20monkey%20wrench%20in%20the%20spokes%20for%20people%20who%20use%20this%20stuff%20daily.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1123967%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1123967%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help%20and%20link.%20They%20tried%20SUMIFS%20with%20one%20cell%20then%20copy%20for%20the%20whole%20range%2C%20it%20worked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%2C%3C%2FP%3E%3CP%3EDz%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120380%22%20slang%3D%22en-US%22%3ERe%3A%20!SPILL%20error%20with%20SUMIFS%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120380%22%20slang%3D%22en-US%22%3EI'm%20sorry%20but%20I%20care%20to%20disagree.%20Version%20differences%20have%20always%20been%20there%20to%20cater%20for%20for%20trainers.%20Like%20in%20the%20past%20you%20need%20to%20adapt%20your%20setup%20to%20your%20training%20audience.%20ALl%20that%20has%20changed%20is%20the%20way%20how%20you%20change%20your%20incarnation%20of%20Office.%20See%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fnl-nl%2FDeployOffice%2Foverview-of-update-channels-for-office-365-proplus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fnl-nl%2FDeployOffice%2Foverview-of-update-channels-for-office-365-proplus%3C%2FA%3E%3C%2FLINGO-BODY%3E
Dzung Vu
Occasional Contributor

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

18 Replies
Highlighted
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?
Highlighted
Highlighted

@Dzung Vu 

That's the imitation of the case

image.png

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.

Highlighted
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.
Highlighted

@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.

Highlighted

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

Highlighted

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?

Highlighted
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.
Highlighted

@Sergei Baklan 

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

 

Cheers

Highlighted

@Jan Karel Pieterse 

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

Highlighted

@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.

Highlighted
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
Highlighted

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

 

Highlighted

@Dzung Vu , glad to know you sorted this out

Highlighted

@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:

 

clipboard_image_0.png

Highlighted

@Patrick2788 

Actually that is also dynamic array

Highlighted

@Sergei Baklan 

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

Highlighted
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
Related Conversations
MSTeams Groups in outlook with macros disabled
ReadyorNot in Microsoft Teams on
0 Replies
Unknown Login Error
Alex_P3462 in Microsoft Teams on
0 Replies
Spelling Pop-Up Stuck on SharePoint List
aricornish in SharePoint on
0 Replies
Problem with OneDrive
esholder in OneDrive for Business on
5 Replies