Forum Discussion

Copper Contributor

Jan 20, 2020

Solved

!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 (ce...

Formulas and Functions

SergeiBaklan
Jan 20, 2020
Dzung Vu

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

Diamond Contributor

Jan 20, 2020

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.

JKPieterse
Silver Contributor
Jan 20, 2020
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.
- SergeiBaklan
  Diamond Contributor
  Jan 20, 2020
  JKPieterse
  
  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.
  - Zulark
    Copper Contributor
    Aug 25, 2020
    SergeiBaklanResponding for visibility as this worked for me.
    
    In my case I have a row of SUMIF formulas, which all want to Spill directly onto the cell immediately to their right, which is of course occupied by another SUMIF formula.
    
    Annoyingly, the "second value" that the formula's supposedly returned that they're attempting to spill is 0! Obviously a bug or oversight on Microsoft's part.
    
    By wrapping the whole SUMIF formula in a SUM formula it fixes it and doesn't attempt to spill a 0 value into the adjacent cells, so stops returning a SPILL error. Infuriating issue!

Resources