Spreadsheet Practice

How to sum until the first blank cell in Excel

Excel formula

=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))

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

Result on the example
$6,005 in E1
Works in
Excel 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

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.

=SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))

Result6005

  1. B2:B98 rows × 1 col: $1,500, $2,840, $1,200, $465, …

    A range: 8 cells in one column.

  2. B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …

    The = operator tests whether the two sides are equal.

  3. XMATCH(TRUE,B2:B9="")5

    XMATCH: Modern MATCH: exact by default.

    • lookup_valueTRUE
    • lookup_arrayB2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
  4. XMATCH(TRUE,B2:B9="")-14

    The - operator subtracts.

  5. TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1){4 rows × 1 col} 1500, 2840, 1200, 465

    TAKE: First (or last, if negative) rows or columns.

    • arrayB2:B98 rows × 1 col: $1,500, $2,840, $1,200, $465, …
    • rowsXMATCH(TRUE,B2:B9="")-14
  6. SUM(TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1))6005

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1TAKE(B2:B9,XMATCH(TRUE,B2:B9="")-1){4 rows × 1 col} 1500, 2840, 1200, 465

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(SUM(ARRAY_CONSTRAIN(B2:B9,MATCH(TRUE,B2:B9="",0)-1,1))). 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.

FormulaResultWhen to use it
=SUMPRODUCT((ROW(B2:B9)<ROW(B2)+MATCH(TRUE,INDEX(B2:B9="",0),0)-1)*B2:B9)$6,005Every 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)="".

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.

Sum until the first blank cell

In E1, add up the costs from B2 down to the row before the first empty cell.

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

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.