How to Sum a Row Until the First Blank Cell in Excel
Question details
Calculate a partial row total that automatically stops summing when it encounters the first blank cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Working with data templates containing multiple rows with variable-length data, where the required total must exclude any data appearing after an empty cell gap.
- Observed behavior
- Users need a dynamic formula that evaluates each cell in a sequence and outputs the accumulated sum up to the first empty cell, rather than summing the entire row or column blindly.
Verify that you are using a recent version of Excel (such as Microsoft 365 or Excel 2021) that supports dynamic array functions, as the BYROW, SCAN, and XMATCH formulas rely on this calculation engine.
Use BYROW and SCAN for Multiple Rows (Microsoft 365)
This is the most efficient method for calculating partial sums across multiple rows of variable-length data simultaneously using a dynamic array formula.
This dynamic array formula iterates through each row in your selected data range. It calculates a running total and uses XLOOKUP to stop the calculation exactly where the first blank cell is detected.
Click on the top cell of an empty column directly adjacent to your data range where you want the partial sums to appear.
Enter the formula: =BYROW(data,LAMBDA(row,LET(accumulated,SCAN(0,row,LAMBDA(x,y,SUM(x,y))),rowTotal,SUM(row),XLOOKUP(TRUE,ISBLANK(row),accumulated,rowTotal)))) into the formula bar. Replace the word 'data' with your actual target range (e.g., A1:F100).
Press Enter. The formula will automatically spill the results down the column, providing one partial sum for each row up to the first blank.
Use SUM, OFFSET, and XMATCH for a Single Range
A targeted and simpler approach if you only need to calculate the sum of a single specific column or row until a blank cell is encountered.
Handle Dynamic Array Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical functions and dynamic arrays, allowing you to execute complex partial sum calculations efficiently. Enjoy a familiar interface and seamless compatibility with all your data files.
- 1. Open Your Data: Download and launch WPS Spreadsheet, then open your existing workbook containing the variable-length rows.
- 2. Input the Formula: Select the destination cell and paste your preferred dynamic array formula (like BYROW or SUM-OFFSET).
- 3. Get Instant Results: Press Enter to instantly execute the calculation. The results will seamlessly spill or populate exactly as they do in modern spreadsheet standards.
Frequently Asked Questions
Why does the formula return a #NAME? error?
The #NAME? error usually occurs if you are using an older version of Excel that does not support modern dynamic array functions like BYROW, SCAN, LAMBDA, or XMATCH. You will need Microsoft 365 or Excel 2021 and later to use these specific solutions.
Can I sum a column until the first blank instead of a row?
Yes. The second solution using SUM, OFFSET, and XMATCH is specifically designed for a single vertical range (column). Just update the range references to your specific column, for example, changing A1:A100 to B1:B100.
What if my data sequence ends with a zero instead of a blank cell?
If the end of your valid data is marked by a zero, you can simply modify the logic in the formulas. For the XMATCH method, change the condition from A1:A100="" to A1:A100=0. The formula will then stop summing when it hits the first zero.
