# How to sum until the first blank cell in Excel

Find the first blank and take the rows above it: `=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))`.

**Excel:** `=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(SUM(ARRAY_CONSTRAIN(B2:B9,MATCH(TRUE,B2:B9="",0)-1,1)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Item | Cost |  | Booked total |
| Venue deposit | 1500 |  |  |
| Catering | 2840 |  |  |
| Photographer | 1200 |  |  |
| Flowers | 465 |  |  |
|  |  |  |  |
| Not booked yet |  |  |  |
| Live band | 3200 |  |  |
| Photo booth | 650 |  |  |

Formula in E1: `=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))`

Result: $6,005

## How it works

`B2:B9=""` is TRUE for empty cells, and XMATCH finds the first TRUE: position 5. TAKE keeps the 4 rows above it, and SUM adds them, so the unconfirmed items below the gap stay out. Add a booking above the gap and the total grows.

## Other ways

- `=SUMPRODUCT((ROW(B2:B9)<ROW(B2)+MATCH(TRUE,INDEX(B2:B9="",0),0)-1)*B2:B9)`: Every version: INDEX(…,0) lets MATCH search the TRUE/FALSE array without Ctrl+Shift+Enter.

## Common mistakes

- If there is no blank in the range, XMATCH returns #N/A. Extend the range one row past the data, or wrap it in IFERROR.
- A cell that holds a space is not blank, so the sum runs past it. Clean the column or test with `TRIM(B2:B9)=""`.

## Questions

**How do I sum from the first blank to the end instead?**

Use DROP: `=SUM(DROP(B2:B9,XMATCH(TRUE,B2:B9="")))`.

**How do I sum until a specific label instead of a blank?**

Search for the label: `=SUM(TAKE(B2:B9,XMATCH("Not booked yet",A2:A9)-1))`.

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/sum-until-the-first-blank-cell