Forum Discussion

Dzung Vu's avatar
Dzung Vu
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...
excel
Formulas and Functions
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 20, 2020

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.

JKPieterse's avatar
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's avatar
    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's avatar
      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!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 25, 2020

        Zulark 

        I can't agree that is the bug. Formula not always return an array, if array now always with two elements, if with two elements not always second element is zero. It very depends on the way how you build the formula.

         

        If you need only first element you may add in front implicit intersection character like

        =@SUMIF(...
    • MichaelBag's avatar
      MichaelBag
      Copper Contributor
      Aug 18, 2020

      placing a Sum formula around the original sumifs does not work with this  SergeiBaklan .  Wonder if anyone can assist?   SUMIFS($U$5:$U$35,Y1,$V$5:$V$35)

       

      I have formulas that simply no longer work.  If they wanted a new feature, introduce a new feature...do not break the tried and true methods.  Wasted an hour hunting through help forums for no advancement other than to confirm its a microsoft change.  Feels like a serious bug to me.

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Jan 20, 2020

      SergeiBaklan perhaps, but your example returns a single result

      • MichaelBag's avatar
        MichaelBag
        Copper Contributor
        Aug 18, 2020

        A single result is exactly what I need JKPieterse How do we do this?  How do I make a simple formula work?  e.g. SUMIFS($U$5:$U$35,Y1,$V$5:$V$35)

Resources