# How to reference a spilled range in Excel

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.

**Excel:** `=SUMIFS(C2:C11,B2:B11,D2#)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(SUMIF(B2:B11,FILTER(D2:D11,D2:D11<>""),C2:C11))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Region | Sales | Region | Total |
| 2026-09-01 | East | 1260 | =UNIQUE(B2:B11) |  |
| 2026-09-02 | West | 810 |  |  |
| 2026-09-03 | East | 1980 |  |  |
| 2026-09-04 | North | 540 |  |  |
| 2026-09-05 | West | 1530 |  |  |
| 2026-09-08 | South | 990 |  |  |
| 2026-09-09 | North | 2250 |  |  |
| 2026-09-10 | East | 720 |  |  |
| 2026-09-11 | South | 450 |  |  |
| 2026-09-12 | West | 1710 |  |  |

Formula in E2: `=SUMIFS(C2:C11,B2:B11,D2#)`

Result (spills 4 × 1): $3,960; $4,050; $2,790; $1,440

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

## Other ways

- `=SUMIFS(C2:C11,B2:B11,UNIQUE(B2:B11))`: Repeats the UNIQUE instead of pointing at it. Same result, but two formulas to keep in step.
- `=SUMIFS(C2:C11,B2:B11,D2:D5)`: 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:D5` instead misses regions added later, or picks up blanks when the list shrinks.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/reference-a-spilled-range