Calculation Issues

How to Sum Each Row in a Dynamic Excel Range

John Wilson Oct 14, 2026 871 views

Question details

The user needs to calculate the total for each row within a dynamic range so that the results automatically update and spill into adjacent cells as data changes.

How to Sum Each Row in a Dynamic Excel Range

Product: Excel
Device & OS: not provided
Scenario: Working with multi-column dynamic data where row-by-row totals are needed without manually dragging formulas.
Observed behavior: Requires a single formula solution that calculates sums per row and spills the totals downward automatically.

Before you start

Ensure your version of Excel supports dynamic array formulas, such as Microsoft 365 or Excel 2021, as the BYROW and LAMBDA functions are required for the primary method.

Solution 1Recommended

Use BYROW and LAMBDA Functions

The most robust and flexible way to sum dynamic rows and spill the results automatically.

The BYROW function applies a custom LAMBDA function to each row in a specified array. This is ideal for dynamic arrays because you only need to write the formula once, and it will automatically adjust and spill results as the source range expands.

Select the target cell

Click on the cell where you want the first row total to appear (e.g., E2). Ensure there are enough empty cells below it for the results to spill without obstruction.

Enter the BYROW formula

Type =BYROW(A2:C4, LAMBDA(a, SUM(a))) into the formula bar. Replace A2:C4 with your actual dynamic range or dynamic array reference (like A2#).

Apply the formula

Press Enter. The formula will calculate the sum for each row in the specified array and automatically spill the total values downward.

Dynamic Updates: If you replace the hardcoded range with a dynamic array reference (e.g., A2#), the spill range will automatically expand or contract as your source data changes.

Solution 2

Use the MMULT Function for Specific Column Counts

An alternative approach for older compatible versions of Excel that supports array calculations based on matrix multiplication.

Efficient Spreadsheet Calculations

Easily Sum Dynamic Ranges with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas, including BYROW, LAMBDA, and MMULT. This allows you to streamline complex calculations just as you would in other major office suites.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the dynamic range data.
2. Apply the dynamic array formula: Select an empty column next to your data and input your =BYROW(array, LAMBDA(a, SUM(a))) formula into the top cell.
3. Save and share: Press Enter to generate the spilled results, then save the document flawlessly in .xlsx format without losing any calculation logic.

Full support for advanced dynamic array formulas like BYROW and LAMBDA to sum rows seamlessly.Complete compatibility with Microsoft Excel (.xlsx) file formats.Free to use with an intuitive, familiar interface.Lightweight application equipped with robust data analysis tools.

Frequently Asked Questions

Why did my spilled array result in a #SPILL! error?

A #SPILL! error occurs when existing text, spaces, or merged cells are blocking the path where the formula needs to spill its downward results. Select the cells below your formula and clear them to allow the formula to expand.

Why am I getting a #NAME? error with BYROW?

The #NAME? error usually means your version of Excel does not support the newer BYROW or LAMBDA functions. You will need to use a compatible version like Microsoft 365, Excel 2021, or the latest version of WPS Office.

Can I use BYROW to calculate averages instead of sums?

Yes. The LAMBDA function inside BYROW allows you to apply any aggregate calculation to each row. Simply replace SUM(a) with AVERAGE(a), MAX(a), or MIN(a) to change the calculation type.

Excel & Data Problems