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

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

Resources