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

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

        MichaelBag Your example has a multi-cell reference in the criteria argument of SUMIFS (the $V$5:$V$35 reference) and hence makes Excel return a multi-cell result. I think what you meant is
        =SUMIF($U$5:$U$35,Y1,$V$5:$V$35)
        (note I wrote SUMIF, not SUMIFS)

    • MagdaWebb55's avatar
      MagdaWebb55
      Copper Contributor
      Jan 20, 2020

      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?

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Jan 20, 2020
        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.

Resources