SUMIFS Spill Error

Question

SUMIFS Spill Error

Venkata Marella 0

Hi,

I'm getting a spill error for SUMIFS. Strangely, if I change the column then there is no error. How do I fix it?

Barry Schwarz 5,861 Reputation points

2026-07-15T16:04:21.2666667+00:00

A spill error implies that your array to be summed has two dimensions. For us to help, you need to show us the actual formula and a screen shot of the cells in the area where the formula is entered.
Venkata Marella 0 Reputation points

2026-07-15T16:41:23.4633333+00:00

screenshot07152026.pdfThank you Barry and please see attached. Formula works fine for column B which is actual data column in the source worksheet (first screen shot). For column C there is a spill error as it is budget data column (second screen shot). The actual and budget data are in seperate columns in the sorce worksheet.

3 answers

Your answer

Barry Schwarz 5,861 Reputation points

2026-07-15T16:04:21.2666667+00:00

A spill error implies that your array to be summed has two dimensions. For us to help, you need to show us the actual formula and a screen shot of the cells in the area where the formula is entered.
Venkata Marella 0 Reputation points

2026-07-15T16:41:23.4633333+00:00

screenshot07152026.pdfThank you Barry and please see attached. Formula works fine for column B which is actual data column in the source worksheet (first screen shot). For column C there is a spill error as it is budget data column (second screen shot). The actual and budget data are in seperate columns in the sorce worksheet.

Answer 1

From Google AI

A SUMIFS formula produces a spill error when an array of criteria (such as {"Apples","Oranges"}) is passed into it instead of a single value. This tricks modern Excel into trying to output multiple calculation results into adjacent cells, but the area is blocked by surrounding data.

In your formula, the criteria argument is StatOpr. StatOpr is defined as the area from A6 to A33, an array of 28 values. Your formula is trying to generate a vertical array of 28 sums. At least one of the 28 cells that would hold this array of sums has a value so the first cell (the one containing the formula) is set to #SPILL.Are you really trying to generate 28 sums with one formula? If so, remove any values in the 27 cells immediately below the cell containing the formula. Otherwise, tell us the real intent of the formula and we can help you achieve that.

Answer 2

Hello @Venkata Marella

Based on your screenshot, the formula appears to be:

=SUMIFS(CombinedCYAMT,CombinedSMAP,StatOpr)

and it works for the "Actual" column but returns "#SPILL!" for the Budget column when you switch the sum range.

Since the formula works with the "Actual" column but returns "#SPILL!" with the Budget column, the most likely cause is that the Budget named range is defined differently (for example, it references multiple columns/rows or a dynamic array). In that case, SUMIFS may attempt to return multiple results instead of a single value, resulting in a spill error.

For example, if:

CombinedCYAMT = Sheet1!B2:B100

CombinedSMAP = Sheet1!A2:A100

the formula returns a single value as expected. However, if the Budget range is defined as:

CombinedBudget = Sheet1!C2:D100

or refers to a formula that spills multiple values, SUMIFS can return a spill error.

As a quick troubleshooting step, go to Formulas > Name Manager and compare the definitions of the Actual range, Budget range, and CombinedSMAP. Ensure they all have the same number of rows and that the Budget range references only a single column. This is the most common cause in scenarios like this.

User's image

For your reference:

Use the Name Manager in Excel | Microsoft Support

How to correct a #SPILL! error | Microsoft Support

If the issue still remains after trying all the steps above, please share a screenshot or provide more detailed information. I will check the situation and continue to support you.

Kind regards,

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in "our documentation" to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Venkata Marella 0

screenshot07152026.pdf

Venkata Marella 0 Reputation points

2026-07-16T13:43:09.6233333+00:00

Hi Alina,

Thank you so much but I havent used any dynamic array it's only "one" column of data ; instead of actual amounts it's budget amounts that is all. Please see attached range name reference screen shot below. I double checked to make sure that the reference for both the names are exatly the same except for actual it's column "B" and for budget it is Column "C" as shared in the earlier screen shot.

Screenshot1.pdf
Venkata Marella 0 Reputation points

2026-07-16T13:49:25.0966667+00:00

Hi Alina,

Thank you so much but I havent used any dynamic array it's only "one" column of data ; instead of actual amounts it's budget amounts that is all. Please see attached range name reference screen shot below. I double checked to make sure that the reference for both the names are exatly the same except for actual it's column "B" and for budget it is Column "C" as shared in the earlier screen shot

Screenshot1.pdf
Rory Archibald 18,965 Reputation points Volunteer Moderator

2026-07-17T09:39:57.01+00:00

From your screenshot you can see that the formula is trying to return 5 results, but there is a formula in the cell below the selected one as well. You would need to clear that cell. However, it seems unlikely that the results will be what you want given the layout of your worksheet. If you want the total of all values that match the criteria in question, you should wrap the formula(s) in a SUM function:

=SUM(SUMIFS(CombinedCYAMT,CombinedISMAP,StatOpr))

and

=SUM(SUMIFS(Combined_Budget,CombinedISMAP,StatOpr))
Alina Le 4,095 Reputation points Independent Advisor

2026-07-17T14:40:27.3166667+00:00

Hi

Thank you for your response.

I will try to review this case again and keep you an update soon.
Venkata Marella 0 Reputation points

2026-07-17T20:42:08.3633333+00:00

Noted Rory let me try that..and thanks Alina. What is still puzzling me is the left most column works perfectly fine viz. column B .....I get the correct results whether it's the budget data or actual data. But the moment I use a 2nd column column C I get a spill error both for actual and budget ....
Alina Le 4,095 Reputation points Independent Advisor

2026-07-20T15:43:25.9533333+00:00

Hi

Thank you for the update. You can try doing this in a new sheet to refresh it and test the issue again.
Venkata Marella 0 Reputation points

2026-07-21T14:36:12.0733333+00:00

Hi all,

It'ss fixed. It's just about deleting data or formats so the formula can return the results. Even if the column is blank just select it and hit delete and that's it.....
Alina Le 4,095 Reputation points Independent Advisor

2026-07-22T16:28:44.3+00:00

Hi

I am glad to hear that

If you need any further assistance while using Office 365 apps, please feel free to reach out to this forum again.

Have a nice day!