How to Calculate Progressive Totals Using MMULT or SUMPRODUCT in Excel
Question details
The user needs to calculate progressive or cumulative totals across columns using matrix array functions like MMULT or SUMPRODUCT.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating cumulative row results where ranges change dynamically across columns, requiring a growing horizontal range combined with a vertical range.
- Observed behavior
- SUMPRODUCT may not produce the intended cumulative row result when ranges change dynamically, whereas an MMULT array formula successfully handles growing matrix dimensions.
Ensure your data is organized in contiguous rows and columns, and check your spreadsheet software version to determine if you need to use legacy array formula entry (Ctrl+Shift+Enter).
Use MMULT for Dynamic Progressive Totals
Use the MMULT function combined with INDEX and COLUMN to handle growing horizontal and vertical ranges dynamically.
The MMULT function returns the matrix product of two arrays. When calculating progressive totals where the horizontal range grows with each column, MMULT is mathematically more robust than SUMPRODUCT. By pairing it with the INDEX and COLUMN functions, the array dimensions adjust automatically as you drag the formula.
Click on the first cell where you want the progressive total to appear (for example, cell C5).
Type the formula: =MMULT($C4:C4,$C$11:INDEX($C$11:$C$16,COLUMN(C5)-COLUMN($C$4)+1)) into the formula bar. Make sure to adjust the absolute and relative cell references to match your specific horizontal data rows and vertical multiplier columns.
If you are using Excel 2019 or an older version, you must press Ctrl+Shift+Enter to confirm it as an array formula (curly braces {} will appear). In Microsoft 365 or Excel 2021, simply press Enter.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) to the right to copy the formula across the required columns.
Calculate Progressive Totals Easily in WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced matrix functions like MMULT and SUMPRODUCT, as well as dynamic arrays, making complex data analysis and cumulative calculations simple and efficient.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your data.
- 2. Input the array formula: Select the target cell and type your MMULT or SUMPRODUCT formula into the formula bar.
- 3. Apply and drag: Press Enter to calculate the result, then drag the fill handle to apply the progressive totals across the rest of your columns.
Frequently Asked Questions
Why does my MMULT formula return a #VALUE! error?
This error occurs if the arrays provided to the MMULT function do not have compatible dimensions. The number of columns in your first array must exactly match the number of rows in your second array. Additionally, ensure there are no empty cells or text values in the ranges being calculated.
Can I use SUMPRODUCT instead of MMULT for cumulative totals?
Yes, SUMPRODUCT can be used for cumulative totals in simple scenarios. However, it often struggles when dealing with dynamic matrix multiplication where a horizontal range grows against a vertical range. In those dynamic cases, MMULT combined with INDEX is the preferred method.
Do I always need to press Ctrl+Shift+Enter for array formulas?
No. If you are using modern spreadsheet software like Microsoft 365, Excel 2021, or the latest version of WPS Office, dynamic arrays are supported natively and you only need to press Enter. Older versions of Office software require Ctrl+Shift+Enter to evaluate the array properly.
