Formula Errors

How to Sum Values Until a Blank Cell in Excel

WPS Content Manager Oct 7, 2026 869 views

Question details

The user needs to sum values in a specific column up to the point where a blank cell occurs, allowing them to calculate subtotals for distinct groups of data.

How to Sum Values Until a Blank Cell in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating subtotals for separate groups of data that are divided by blank rows in a spreadsheet.
Observed behavior: A dynamic formula is required to identify the range of each data group and output the correct sum at the end of each group without overlapping the totals from previous groups.

Before you start

Ensure your data is organized consistently in a single column and that the blank cells truly contain no hidden characters or spaces, as these can disrupt the formula's ability to detect breaks between groups.

Solution 1Recommended

Use XLOOKUP and SUM for Dynamic Data Groups

This method is highly efficient for modern Excel versions, using XLOOKUP to dynamically locate the start of the current group and SUM to total the range.

This solution works best in Microsoft 365, Excel 2021, and modern spreadsheet software that supports dynamic array functions like XLOOKUP.

It uses a combination of logical tests to check for empty cells and backwards searching to isolate each data cluster.

Select the target cell

Click on the cell in the adjacent column where you want the first subtotal to appear (e.g., cell C5, corresponding to a blank cell in column B).

Enter the formula

Type the formula =IF(B5="",SUM(XLOOKUP(TRUE,C$1:C4<>"",B$1:B4,,,-1):B5),"") into the formula bar. Adjust the row numbers according to where your data begins.

Apply to the column

Press Enter, then click the bottom-right corner of the cell and drag the fill handle down to apply this logic to the rest of your data groups.

Use XLOOKUP and SUM for Dynamic Data Groups

Reference Locking: Make sure your absolute references (the $ signs in C$1:C4) are correct so the formula doesn't accidentally include previous groups in the new subtotal.

Solution 2

Use OFFSET, MATCH, and ISBLANK for Legacy Versions

If you are using an older version of Excel that does not support XLOOKUP, this combination will find the first blank cell and sum the range above it.

Calculate Data Groups Effectively

Use WPS Spreadsheet to Sum Dynamic Data Ranges Easily

WPS Spreadsheet provides robust support for modern array formulas, including XLOOKUP and dynamic ranges. You can effortlessly manage, group, and calculate complex datasets without performance lags, all within a familiar user interface.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your grouped data.
2. Select the subtotal cell: Click the cell adjacent to the blank row that separates your data groups.
3. Insert the formula: Type the recommended XLOOKUP and SUM formula into the formula bar and press Enter.
4. Fill the series: Drag the fill handle downward to instantly calculate totals for all remaining data groups in the sheet.

Fully compatible with Microsoft Excel formulas and .xlsx files.Native support for advanced functions like XLOOKUP, MATCH, and INDEX.Built-in Subtotal tools for quick calculations without complex coding.Lightweight software with an intuitive, multi-tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula sum multiple groups together instead of resetting?

This usually happens due to incorrect relative and absolute cell references. If the starting point of the lookup range (like C$1) is locked improperly compared to the moving group boundary, the formula continues adding from the very top. Always double-check your $ placements.

Will the OFFSET formula slow down my spreadsheet?

Yes, OFFSET is a volatile function. If you have thousands of rows, recalculating this formula every time a cell changes can cause noticeable performance drops. It is highly recommended to use XLOOKUP or INDEX instead if your software version supports it.

Can I use the Subtotal feature instead of a formula?

Yes. If your data has categories or labels in an adjacent column, you can select your data range and use the built-in 'Subtotal' tool located under the Data tab. This automatically inserts sum formulas at each change in the category without requiring manual formula entry.

Why does my XLOOKUP formula return an error for the first group?

If there is no prior blank cell or criteria to trigger the lookup boundary for the very first group, XLOOKUP may fail to find a match. Ensure your formula starts looking below the header row, or wrap it in an IFERROR function to handle the first block gracefully.

Excel & Data Problems