How to Use Multiple Criteria Arrays with SUMIFS in Microsoft 365
Question details
Summing values based on multiple conditions across different criteria dimensions such as specific locations, school types, or program codes.
- Product
- Microsoft 365
- Device & OS
- not provided
- Scenario
- The user needs to calculate a total sum when data must match several allowed values across multiple columns.
- Observed behavior
- Standard SUMIFS functions apply AND logic, making it difficult to process OR logic across multiple dimensions without using advanced array functions like FILTER and SUM.
Ensure your dataset is formatted as an Excel Table or uses Named Ranges to make complex array formulas easier to read and manage.
Use FILTER and SUM with MATCH for Multiple Dimensions
This is the most flexible and recommended method when you need to match several allowed values across multiple columns using dynamic arrays.
By combining the FILTER, ISNUMBER, and MATCH functions, you can create a powerful formula that applies OR logic within specific columns and AND logic across different dimensions.
Identify the primary column containing the values you want to sum, such as DATA[Eligibility].
Use the MATCH function inside ISNUMBER to check if rows belong to an allowed set of criteria. For example, type ISNUMBER(MATCH(DATA[Location],{"A","B"},0)).
Combine multiple dimensions by multiplying them together: ISNUMBER(MATCH(DATA[Location],{"A","B"},0)) * ISNUMBER(MATCH(DATA[SchoolType],{"D","E"},0)).
Enclose the multiplied conditions within a FILTER function to extract the matching data, then wrap the entire formula in a SUM function to get the total: =SUM(FILTER(DATA[Eligibility], ...)). Press Enter to calculate the result.
Combine Multiple SUMIFS Formulas
Best for simpler scenarios where the criteria combinations are limited, explicit, and do not require complex dynamic arrays.
Master Complex Formulas with WPS Spreadsheet
WPS Office offers a powerful, free Spreadsheet tool that fully supports advanced Microsoft Excel functions, including dynamic arrays, FILTER, SUMIFS, and MATCH. Handle your most complex data analysis seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
- 2. Select the target cell: Click on the cell where you want to calculate the total sum based on multiple criteria.
- 3. Enter the array formula: Input your complex array formula using FILTER and SUM, then press Enter to view the precise calculation.
Frequently Asked Questions
Why does standard SUMIFS fail with multiple criteria arrays in the same column?
Standard SUMIFS uses strict 'AND' logic between its arguments. If you attempt to specify multiple values for the same column directly, it cannot process the 'OR' logic required natively without wrapping the criteria in a curly bracket array and encasing the entire formula in a SUM function.
Can I use cell references instead of hardcoded arrays in the MATCH function?
Yes, instead of typing arrays manually like {"A","B"}, you can select a range of cells (e.g., $F$2:$F$3) as the lookup array within the MATCH function. This makes your dashboard or report fully dynamic.
Does the FILTER and SUM method work in older versions of Excel?
No, the FILTER function is a dynamic array function introduced in Microsoft 365 and Excel 2021. If you are using an older version, you will need to rely on the SUMPRODUCT function to achieve similar results.
