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

excel

Formulas and Functions

office 365

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

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

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