How to reference a spilled range in Excel
Excel formula
=SUMIFS(C2:C11,B2:B11,D2#)Put # after the formula cell: D2# means the whole spill from D2, so =SUMIFS(C2:C11,B2:B11,D2#) totals every region in that list.
- Result on the example
- a 4-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
The # after D2 refers to the whole range the formula in D2 spills into, however many rows that is today. Given that list as its criteria, SUMIFS returns one total per region and spills them beside it. When a new region appears in the data, both columns grow together.
Google Sheets has no # reference, so its version reads the filled cells of an ordinary range.
=SUMIFS(C2:C11,B2:B11,D2#)Result{4 rows × 1 col} 3960, 4050, 2790, 1440
C2:C1110 rows × 1 col: $1,260, $810, $1,980, $540, …A range: 10 cells in one column.
B2:B1110 rows × 1 col: "East", "West", "East", "North", …A range: 10 cells in one column.
D2#4 rows × 1 col: "East", "West", "North", "South"The value in D2#.
SUMIFS(C2:C11,B2:B11,D2#){4 rows × 1 col} 3960, 4050, 2790, 1440SUMIFS: Adds values that meet every condition.- sum_range
C2:C1110 rows × 1 col: $1,260, $810, $1,980, $540, … - criteria_range1
B2:B1110 rows × 1 col: "East", "West", "East", "North", … - criteria1
D2#4 rows × 1 col: "East", "West", "North", "South"
- sum_range
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(SUMIF(B2:B11,FILTER(D2:D11,D2:D11<>""),C2:C11)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(C2:C11,B2:B11,UNIQUE(B2:B11)) | spills 4 × 1 | Repeats the UNIQUE instead of pointing at it. Same result, but two formulas to keep in step. |
| =SUMIFS(C2:C11,B2:B11,D2:D5) | spills 4 × 1 | A fixed range gives the same totals today, but misses a fifth region added later and shows a 0 row if the list shrinks. |
Common mistakes
- The # goes on the cell that holds the formula, the top-left cell of the spill.
D3#returns #REF!. - If the spill is blocked (#SPILL!), every formula that uses
D2#returns #REF! too. Clear the cells below D2. - Referring to a fixed range such as
D2:D5instead misses regions added later, or picks up blanks when the list shrinks.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Reference a spilled range
D2 holds =UNIQUE(B2:B11), which spills the regions. In E2, total the sales for every region in that list, using a reference that grows with it.Questions
Can I use a spilled range in a dropdown?
Yes. In Data Validation, choose List and enter =$D$2# as the source.
How do I refer to one column of a multi-column spill?
Use CHOOSECOLS or INDEX on it: =CHOOSECOLS(D2#,2) or =INDEX(D2#,,2).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.