How to Create a Dynamic Two-Dimensional Sum by Rows and Columns in Excel
Question details
The user wants to calculate dynamic totals organized by both rows (e.g., products) and columns (e.g., weeks) using array functions to output a two-dimensional result.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a cross-tabulation or two-dimensional summary table from a raw dataset of transactions.
- Observed behavior
- Needs a formulaic approach to output a two-dimensional dynamic array result rather than manually dragging standard formulas across rows and columns.
Ensure your version of Excel or WPS Office supports dynamic array functions like MAP, WRAPROWS, and SEQUENCE if you plan to use a single-cell dynamic formula. For older versions, traditional SUMIFS with mixed references is required.
Use MAP and WRAPROWS for a Single Dynamic Array Formula
Create a single formula that dynamically spills a 2D matrix of sums based on dynamic row and column headers.
This method is ideal when your row and column headers are themselves dynamic arrays (e.g., generated by the UNIQUE function). By combining MAP, TOCOL, and WRAPROWS, you can force Excel to iterate through every combination of rows and columns and spill the results into a 2D grid.
Ensure your row headers (e.g., Products in G15#) and column headers (e.g., Weeks in H14#) are set up as dynamic arrays.
Use the MAP function combined with TOCOL and SEQUENCE to iterate through every combination of row and column headers. For example: MAP(TOCOL(IF(SEQUENCE(,COLUMNS(H14#)),G15#)), TOCOL(IF(SEQUENCE(ROWS(G15#)),H14#)), LAMBDA(n,p, ...)).
Inside the LAMBDA function, define your sum logic. For instance, multiply conditions to filter the raw data: SUM((ISOWEEKNUM(Table[Date])=n)*(Table[Product]=p)*Table[Sales]).
Enclose the entire MAP function inside WRAPROWS to convert the resulting 1D array back into a 2D matrix. Use COLUMNS(H14#) as the wrap_count argument: =WRAPROWS(MAP(...), COLUMNS(H14#)).
Use Standard SUMIFS with Mixed References
The simplest and most efficient method when your summary table has physical, fixed ranges for rows and columns.
Calculate Two-Dimensional Dynamic Sums in WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced mathematical functions, including SUMIFS and dynamic arrays, allowing you to build complex 2D summary tables with ease.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your raw transaction data.
- 2. Extract unique headers: Use the UNIQUE function to dynamically extract your row and column headers into your summary sheet.
- 3. Apply the formula: Enter your SUMIFS or dynamic array formula (using MAP and WRAPROWS) in the top-left cell of your summary matrix.
- 4. View the results: Press Enter to instantly calculate and spill your two-dimensional dynamic sum.
Frequently Asked Questions
Why is my WRAPROWS or MAP formula returning a #NAME? error?
This usually means your spreadsheet software version does not support modern dynamic array functions. You will need to use standard SUMIFS formulas or upgrade to a newer version like Microsoft 365 or the latest WPS Office.
Can I use MAKEARRAY instead of MAP for a 2D sum?
Yes, MAKEARRAY can generate a 2D grid based on specified row and column counts. However, it is often less efficient for very large datasets compared to simple SUMIFS or optimized MAP logic.
How do I dynamically extract the weeks and products for the headers?
You can use the UNIQUE function on your raw data columns. For example, =UNIQUE(A2:A100) will return a dynamic list of unique products, which you can then reference in other formulas using the # spill operator (e.g., G15#).
Are dynamic array formulas slower than traditional SUMIFS?
Yes, complex LAMBDA-based iterations like MAP over large datasets can sometimes be slower to calculate than built-in, highly optimized functions like SUMIFS applied to physical ranges.
