01 / 14One Supplier Fell Off the Bottom of the Claim

Dynamic ArraysUNIQUESUMIFS

Excel #SPILL! Error: Causes, Fixes and the # Operator

We Opened One New Account and Quietly Stopped Claiming From a Supplier of Nine Years, Because the List Spilled One Row Further Than the Column Beside It

25 Oct 202614 min read

UsesUNIQUESORTSUMIFSFILTER

Calderhay Wholesale claims a volume rebate from nine suppliers. For three quarters it claimed from eight. Nothing on the sheet was wrong, nothing was red, and £24,587.50 was never asked for.

The claim sheet is two columns. Column H spills the supplier list out of the purchase ledger, and column I totals each supplier's net purchases beside it.

Column H is one formula that writes as many rows as it needs. Column I was eight formulas, typed once. In January a ninth supplier was added, the list grew by a row, and the column beside it did not.

Q3 2026 rebateFigure
Earned on purchases£67,257.75
Claimed from suppliers£58,724.75
Short by£8,533.00
Quarters affected3
Still inside the claim window£8,533.00

Scope. Spill ranges, the # spill operator, #SPILL!, the @ of implicit intersection, and UNIQUE, SORT, FILTER and SUMIFS in Microsoft 365 and Excel 2021 and later.

Excel 2019 and earlier have no dynamic arrays at all. Section 11 says what those builds make of a file written this way.

Throughout, Lines is the purchase ledger table, column H holds the supplier list and column I the totals beside it.

01One Supplier Fell Off the Bottom of the Claim

Calderhay buys from a short list of suppliers and claims a volume rebate from each one at the end of every quarter. The rates sit in the supply agreements; the purchases sit in a ledger table called Lines.

The claim sheet was built in 2023 with eight suppliers on it. H2 held =SORT(UNIQUE(Lines[Supplier])), which spilled eight names down to H9, and I2 held =SUMIFS(Lines[Net],Lines[Supplier],H2), filled down to I9.

In January 2026 the buying team opened an account with Calderbrook Organics. The list sorted it into third place, every name below it moved down one row, and the list now ended at H10.

Column I still ended at I9. Each of its eight formulas re-read the name that had moved into its row, so all eight totals were right — for the first eight names on a list of nine.

Harlings Dry Goods is the ninth name, and no formula points at it. It had supplied Calderhay for nine years, on the best rebate rate the firm holds, and it was claimed every quarter until the quarter somebody else was added.

Nine Suppliers, Eight Totals and One Rebate Nobody Asked For

Calderhay Wholesale's quarterly rebate claim, in A1:F13. Column B is the cell on the claim sheet that holds each supplier's name, written by one formula in H2 that spills as far down as the ledger needs. Column D is the rebate each supplier's purchases earned under its supply agreement; column E is what was actually claimed. The totals column beside the list ends at I9, so the ninth name has no formula pointing at it, and the three rows at the bottom are the quarter, the two quarters before it and the part still inside the ninety-day claim window.

ABCDEF
1
Supplier
Spill cell
Net purchases
Rebate earned
Rebate claimed
What happened
2
Abbeymoor Produce
H2
412600
12378
12378
Produce and salad, 3.0% on everything over £250,000 a quarter. First alphabetically since the sheet was built, so its total has sat in I2 for three years and has never been wrong.
3
Brindlewood Meats
H3
338900
8472.5
8472.5
Butchery, 2.5% flat. Second on the list before January and second after it, because the new account sorts below it.
4
Calderbrook Organics
H4
96400
2410
2410
The account opened in January 2026 at 2.5%. The smallest supplier on the sheet, claimed correctly every quarter, and the reason everything below it moved down a row.
5
Carrowdale Bakery
H5
196250
4906.25
4906.25
Bakery, 2.5%. Sat in H4 for three years and has been in H5 since January; its total followed it, because the SUMIFS in each row reads whatever name is in column H beside it.
6
Dunmorey Fisheries
H6
271400
8142
8142
Fish and shellfish, 3.0%. Moved down a row in January along with everything under Calderbrook, and claimed in full in every quarter since.
7
Esker Fine Foods
H7
154800
3870
3870
Speciality and deli, 2.5%. The second smallest account and the one most people assumed would be the one to go missing.
8
Fairholm Beverages
H8
389700
11691
11691
Soft drinks and juice, 3.0%. The second largest rebate on the sheet, which is why nobody reading the claim total thought it looked light.
9
Glenrothan Frozen
H9
228500
6855
6855
Frozen goods, 3.0%. The last row the totals column reaches: I9 is the final SUMIFS anybody ever filled down, and it reads H9.
10
Harlings Dry Goods
H10
243800
8533
0
Dry goods and ambient, 3.5% — the best rate Calderhay holds, on an account that has supplied it for nine years. It sorts last, so January pushed it to H10 and no formula has pointed at it since.
11
Q3 2026 claim
9 names, 8 totals
2332350
67257.75
58724.75
The quarter as submitted. The ledger foots, every total agrees with it to the penny, and the claim is short by exactly one supplier's rebate.
12
Q1 and Q2 2026
Same fault, twice
458700
16054.5
0
Harlings supplied £231,800.00 and £226,900.00 in the first two quarters of 2026 and earned £8,113.00 and £7,941.50. Both claim windows have closed.
13
Still claimable
Q3 only
243800
8533
0
The supply agreement allows ninety days from the quarter end. Q3 is inside it; the other £16,054.50 is not, and was written off in October.

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

02A Spilled List Is One Formula, Not Nine Cells

=SORT(UNIQUE(Lines[Supplier])) lives in one cell. Everything under it is output: Excel writes the answer into as many cells as it needs, and those cells hold no formula of their own.

Click H5 and the formula bar shows the formula greyed out, with H2 as the only cell you can edit. Delete H2 and the whole list goes. That block is the spill range, and the data decides its size.

A column of filled-down formulas is the opposite. Each cell is its own formula, each was put there by a person, and the column is exactly as long as the day somebody dragged it.

Those two things side by side is the fault. One grows on its own and the other needs a person, so they agree only until the data changes.

Scenario: Click a cell below the top of a spilled list in your own workbook. A greyed formula bar means you are inside a spill range; type =ROWS(H2#) in a spare cell to see how many rows it writes today.

Try it in the grid

03H2# Is the Range That Grows With It

Excel has a reference for the whole spill: the top cell with a # after it. H2# is every row the formula in H2 currently writes — nine rows today, ten tomorrow, without anybody editing anything.

So the totals column should have been one formula as well:

=SUMIFS(Lines[Net],Lines[Supplier],H2#)

That spills its own answer down from I2, one total for each name, however many names there are. The claim total then becomes =SUM(I2#), which reads whatever the column wrote.

Written asRows it coversWhat a ninth supplier gets
SUMIFS filled down I2:I9Eight, foreverEight totals, nine names
=SUMIFS(Lines[Net],Lines[Supplier],H2#)As many as H2 writesNine totals
=SUM(I2:I9)Eight, foreverMisses the ninth
=SUM(I2#)The whole spillIncludes it

Scenario: Put =ROWS(H2#) beside =ROWS(I2:I9) on your own sheet. Any time those two disagree, something on the right-hand side is being left out of a total.

Try it in the grid

04What #SPILL! Means and How to Clear It

#SPILL! is the opposite problem, and it is the loud one. The formula wants to write into cells that are not empty, so rather than overwrite them it writes nothing and returns an error instead.

Click the warning triangle and Excel names the reason. Spill range isn't blank is the common one, with Select Obstructing Cells under it, which jumps the cursor to whatever is in the way — usually a stray space or an old heading.

The other four are worth knowing by name, because each has a different fix.

What the triangle saysWhat is actually wrong
Spill range isn't blankA value, a space or a note sits in the output
Spill range has merged cellsA dynamic array cannot write into a merged cell at all
Spill range is unknownThe size depends on something volatile, such as RANDBETWEEN
Spill range is too bigThe result runs past the last row or column of the sheet
Spill range in tableThe formula is inside an Excel Table, which cannot hold a spill

Scenario: Type anything into the cell directly under a spilled list and watch the whole list collapse to #SPILL!. Press Ctrl+Z, then count the clear rows you actually have beneath it.

Try it in the grid

05The @ Excel Writes to Stop a Spill

Excel will sometimes stop a formula spilling rather than report #SPILL!, by writing an @ in front of it. =@SORT(UNIQUE(Lines[Supplier])) returns one name instead of a list, and it returns it without an error.

That @ is implicit intersection: take the single value on the same row as the formula and throw the rest away. It is how every version of Excel before 2019 read a range handed to a one-value argument.

It appears when an older workbook is opened in 365, when a formula is pasted into a Table, and whenever somebody copies a formula out of a sheet written on an older build.

A number is the dangerous answer. #SPILL! stops a claim being submitted; @ totals one row, leaves the rest of the list unasked for, and nobody checks a cell that holds a number.

Scenario: Press Ctrl+F, set Look in to Formulas, and search your workbook for @. Delete one and see whether the cell was answering with a list or with a single row of it.

Try it in the grid

06Spills Do Not Work Inside a Table

An Excel Table manages its own rows: insert one and every formula column fills down into it. A dynamic array manages its own rows too, and the two cannot both be in charge of the same cells.

So a formula that spills, typed into a Table column, returns #SPILL! with Spill range in table as the reason. Excel usually offers the @ instead, which turns the formula into a one-row answer and no error.

The claim sheet is not a Table, which is the only reason H2 works at all. The ledger it reads is a Table, and that is the right way round: Tables for the data, plain cells for anything that spills.

Converting back. Table Design → Convert to Range turns a Table into ordinary cells, and structured references pointing at it become ordinary ranges.

Do that to the sheet holding the spill, never to the sheet holding the data. A ledger that is a Table is what lets Lines[Supplier] grow when a row is appended.

Scenario: Type =UNIQUE(A2:A20) into a spare column of any Table in your workbook and read the error. Then type the same formula outside the Table and watch it spill.

Try it in the grid

07Never Read a Spill by Row Number

A sorted list has no fixed addresses. H4 was Carrowdale Bakery for three years and became Calderbrook Organics in January, and nothing in the workbook changed to make that happen.

Any formula pointing into a spill with a row number is a bet that the sort will not move. =H4 and =INDEX(H2#,4) are two ways of making that bet, and both of them lose quietly rather than loudly.

Read it by name instead:

=XLOOKUP("Harlings Dry Goods",H2#,I2#)

That follows the supplier wherever the sort puts it, and returns #N/A if the supplier stops appearing in the ledger at all — which is a question somebody should be asked.

Scenario: Count the cells in your workbook that point into a spilled block with a row number. Replace one with an XLOOKUP against the label column and check the figure does not move today.

Try it in the grid

08Leave Room Under Anything That Spills

A spill needs its room before it needs it. A nine-row list has to be able to become a ten-row list on the same sheet, which means the cell under H10 has to be empty on the day that happens.

So nothing goes directly beneath a spilled block. No total, no note, no second table. The claim total belongs above the list or in a column of its own, where growth cannot reach it.

A grand total under a dynamic list is the most common #SPILL! in a finance workbook, because under the column is where a total has always gone.

Where the total goesWhat happens when the list grows
Directly under the listThe whole list becomes #SPILL!
Two rows under the listFine until the list grows by two
Above the list, in H1Never in the way
A column over, =SUM(I2#)Never in the way, and reads the whole spill

Scenario: Move one grand total from under a spilled list into the row above it and change its formula to read the spill with #. The figure should not move; the risk should.

Try it in the grid

09Three Checks That Catch a Short Column

The claim went out eight times and footed eight times, because every figure on it was right. What was missing was a row, and no total can report a row that was never added to it.

Count the rows on both sides. =ROWS(H2#)-COUNT(I2:I9) is zero while the two agree and non-zero the moment they stop. Park it beside the claim where somebody will read it.

Reconcile to the source. =SUM(Lines[Net])-SUM(I2#) should be zero every quarter. Every pound in the ledger belongs to exactly one supplier, so those are two routes to the same number.

Count the names. =COUNTA(UNIQUE(Lines[Supplier])) reads the ledger rather than the claim sheet, so a supplier appearing for the first time changes it in the week it happens.

Scenario: Put the reconciliation in a cell beside your own summary and format it so that a non-zero result goes red. It is one formula, and it is the one that would have caught this in January.

Try it in the grid

10When a Fixed Range Is the Right Answer

A spill is not always what you want. A claim form with eight numbered lines, a report laid out to fit a page, a template somebody types into: all of those need a fixed shape, and a block that grows would break them.

The question is which side decides the size. If the data decides, use a spill and read it with #. If the layout decides, use fixed cells and check that the data still fits inside them.

What cannot happen is one of each. A list that grows on its own, read by a column that does not, is the fault this whole article is about, and it reports a sensible-looking number while it does it.

Scenario: Pick one summary in your workbook and decide out loud which of the two it is. Then make both of its columns match that decision, and put =ROWS(H2#) somewhere to prove it.

Try it in the grid

11Older Excel, the Web and Shared Files

Spill ranges need Microsoft 365 or Excel 2021. Open this claim sheet in Excel 2019 and UNIQUE, SORT and the # operator are all unknown names, so the list is a column of #NAME? and every total beside it is zero.

Excel for the web spills normally and is a fair test of what a colleague on 365 sees. Google Sheets has its own array behaviour and its own function list, so a round trip through it is not a round trip.

A shared workbook makes the growth somebody else's doing. One person appends a ledger row, the list grows on everybody's screen, and the person who owns the claim sheet is not the person who made it short.

Scenario: Open the workbook in Excel for the web and read the row count of the spill. If it differs from a colleague's desktop, their build is the difference rather than the data.

Try it in the grid

12Nine Traps

  1. A spilled list beside a filled-down column. One side grows by itself and the other does not, and the gap between them is silent.
  2. =SUM(I2:I9) over a dynamic block. The total is right about the rows it was given and says nothing at all about the rest.
  3. A grand total directly under a spill. The list does not grow by a row; it becomes #SPILL! and the whole thing disappears.
  4. Reading a sorted list by row number. H4 is whichever supplier sorts fourth today, and sorting is what the formula is for.
  5. Treating @ as punctuation. It is a call that throws away everything but the one value on its own row.
  6. Typing a spilling formula into a Table. Tables and dynamic arrays both own their rows, so one of the two has to go.
  7. Merged cells anywhere in the output. A spill will not write into them at all, whatever else is true about the sheet.
  8. Assuming #SPILL! is the only failure. The error is the version of this fault that announces itself.
  9. Sending the workbook to Excel 2019. No spills, no #, and a column of #NAME? where the suppliers used to be.

13Five Exercises

  1. Reproduce it. Spill eight names with =SORT(UNIQUE(A2:A40)), fill a SUMIFS down eight rows beside it, then add a ninth name that sorts into the middle and read the total.
  2. Fix it with #. Replace the eight formulas with one =SUMIFS(Lines[Net],Lines[Supplier],H2#), then add the ninth name again and watch the column grow.
  3. Break it on purpose. Type a space into the cell under the list, read the triangle's reason, and use Select Obstructing Cells to find the space.
  4. Meet the @. Type =@SORT(UNIQUE(A2:A40)) and compare what it returns with the same formula without the @ in front of it.
  5. Reconcile it. Write =SUM(Lines[Net])-SUM(I2#) beside your summary and confirm it reads zero before and after each change above.

What to Take Away

Every figure on every one of those claims was correct. Eight suppliers, eight totals, eight rebates, each one agreeing with the ledger to the penny.

The sheet had nine suppliers on it. One formula had grown to say so and the column beside it had not, and £24,587.50 of rebate went unclaimed across three quarters.

A spill range decides its own size. Anything reading it has to be told to do the same, with # rather than a row number, and anything sitting beneath it has to move.

£8,533.00 of the £24,587.50 is still inside the ninety-day claim window. The rest expired quietly, in a workbook that never once showed an error.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge