How to Build Dynamic Excel SUMIFS Across Rows and Columns with MMULT
Question details
Build a dynamic array formula to aggregate trial balance values by account or entity across variable monthly periods without using an intermediate calculation sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Aggregating complex trial balance data requiring dynamic intersections of both row elements (like GL codes and entities) and column elements (like variable monthly date periods).
- Observed behavior
- The user needs to construct a cross-tabular sum that dynamically spills using multiple criteria for both axes, rather than manually building static SUMIFS formulas or intermediate calculation sheets.
Ensure you are using a version of Excel or WPS Spreadsheet that supports Dynamic Array functions, as formulas like TOROW, TOCOL, SEQUENCE, and MAKEARRAY are essential for these solutions.
Use MMULT to Aggregate Data Across Rows and Columns
An MMULT-based matrix approach calculates complex criteria spanning both rows and columns in a single, lightning-fast step.
Matrix multiplication (MMULT) is powerful but requires matching array dimensions. You can multiply boolean arrays (True/False converted to 1/0 using the double-unary -- operator) to create multi-condition filters.
To add multiple row criteria (such as filtering by both entity and GL code), simply multiply the row masks together within the formula.
Use the TOROW function to check the target accounts. For multiple criteria, multiply them: ((TOROW(C6:C19)=C27:C29)*(TOROW(A6:A19)=C39)).
Use the TOCOL function to bound the start and end dates of the monthly periods: (TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25).
Input the full combined formula =MMULT(--((TOROW(C6:C19)=C27:C29)*(TOROW(A6:A19)=C39)),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25))) into your target output cell.
Use FILTER and MAKEARRAY for Intuitive Dynamic Spilling
If matrix multiplication is too difficult to troubleshoot, you can use modern array functions like FILTER and CHOOSECOLS for an easier-to-read dynamic aggregation.
Perform Advanced Matrix Calculations in WPS Spreadsheet
WPS Spreadsheet natively supports advanced matrix and dynamic array functions like MMULT, SEQUENCE, and FILTER, allowing you to seamlessly process complex financial trial balances and datasets.
- 1. Open your workbook: Launch WPS Spreadsheet and open your trial balance or financial modeling workbook.
- 2. Select the target cell: Click on the primary cell where you want the aggregated matrix results to appear.
- 3. Input the array formula: Type your MMULT or MAKEARRAY formula, ensuring your row and column arrays are perfectly aligned.
- 4. Execute the calculation: Press Enter to execute the calculation. WPS Spreadsheet will automatically spill the results into the adjacent cells.
Frequently Asked Questions
Why does my MMULT formula return a #VALUE! error?
This usually happens when the dimensions of the matrices being multiplied are incompatible. In MMULT, the number of columns in the first array must exactly match the number of rows in the second array.
Can I add more than two criteria to an MMULT formula?
Yes. You can add more criteria by multiplying additional boolean arrays within the row or column masks, for example, (TOROW(A6:A19)=C39)*(TOROW(B6:B19)=D39). Both criteria must evaluate to TRUE for the data to be included.
What does the double dash (--) mean in Excel array formulas?
The double unary operator (--) coerces boolean TRUE/FALSE values generated by condition checks into numeric 1s and 0s. This is required because mathematical functions like MMULT cannot process raw TRUE/FALSE text values.
