Function Problems

How to Use an XLOOKUP Array as SUMIFS Criteria in Excel

Algirdas Jasaitis Oct 21, 2026 868 views

Question details

The user needs to construct a formula that utilizes an array returned by XLOOKUP as the criteria argument in a SUMIFS function, specifically aiming to sum a two-dimensional range representing demand data across several future months.

How to Use an XLOOKUP Array as SUMIFS Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating multiple upcoming months of demand by checking a dynamic array criteria against a dataset containing a multi-column sum range.
Observed behavior: Standard SUMIFS fails to calculate the result when provided with a two-dimensional sum range and an XLOOKUP dynamic array as the criteria.

Before you start

Verify that your spreadsheet software supports dynamic array functions (like XLOOKUP and FILTER), and ensure your sum range and criteria range dimensions align properly.

Solution 1Recommended

Replace SUMIFS with SUM and FILTER for Two-Dimensional Ranges

Use this solution when your demand data spans multiple columns (a 2D range), which natively causes SUMIFS to return a #VALUE! error.

The SUMIFS function requires the sum_range and the criteria_range to have the exact same size and shape. When you try to sum demand data spanning multiple future months (e.g., a 3-column width) based on a single-column criteria range, SUMIFS will fail.

Instead of SUMIFS, wrapping the FILTER function inside a SUM function perfectly handles 2D sum ranges against an XLOOKUP array.

Select the target cell

Click on the empty cell in your worksheet where you want the total calculated demand to appear.

Enter the SUM and FILTER combination

Type `=SUM(FILTER(DemandDataRange, CriteriaRange = XLOOKUP(LookupValue, LookupArray, ReturnArray)))` into the formula bar.

Adjust ranges to your dataset

For example, type `=SUM(FILTER(C2:E10, A2:A10 = XLOOKUP("ProductX", G2:G5, H2:H5)))`. In this example, C2:E10 represents the three future months of demand data.

Execute the formula

Press Enter. The formula will filter the two-dimensional demand range based on the dynamic array matched by XLOOKUP, and calculate the complete sum.

Replace SUMIFS with SUM and FILTER for Two-Dimensional Ranges

Dynamic Array Processing: Using FILTER bypasses the strict sizing limitations of SUMIFS, allowing you to sum multiple columns natively in one step.

Solution 2

Use SUMPRODUCT as a Legacy Alternative

Apply this solution if you need an alternative mathematical approach to handle multi-column sums without relying on the FILTER function.

Advanced Spreadsheet Functions

Easily Handle Dynamic Arrays and XLOOKUP in WPS Spreadsheets

WPS Office Spreadsheets offers full support for modern dynamic array formulas, including XLOOKUP, FILTER, and SUMIFS. Easily calculate complex multi-dimensional criteria with a highly compatible and intuitive interface.

1. Open your workbook: Launch WPS Spreadsheets and open the file containing your multi-month demand data.
2. Select the result cell: Click on the cell where you intend to sum the demand data.
3. Input the dynamic array formula: Type the combined `=SUM(FILTER(...))` or `=SUMPRODUCT(...)` formula incorporating your XLOOKUP criteria directly into the formula bar.
4. Calculate and evaluate: Press Enter. WPS Spreadsheets will instantly process the array elements and display the accurate sum across your specified dimensions.

100% format and formula compatibility with Microsoft ExcelNative support for advanced dynamic array functions like XLOOKUP and FILTERLightweight architecture ensuring fast calculations on large datasetsFree to use with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return a #VALUE! error when using multiple columns?

The SUMIFS function strictly requires the sum_range and all criteria_ranges to have the exact same dimensions (both rows and columns). If you attempt to sum a three-column demand range while applying criteria to a one-column range, Excel will return a #VALUE! error.

Can XLOOKUP return an array of multiple values?

Yes, XLOOKUP is designed to return dynamic arrays. If the return_array argument spans multiple columns or rows relative to the lookup_array, XLOOKUP will spill the results into adjacent cells or pass the entire array into a wrapping formula like SUM or FILTER.

Is FILTER better than SUMIFS for multi-dimensional summing?

Yes, when dealing with arrays of different dimensions (like a 1D criteria range and a 2D sum range), combining SUM and FILTER is much more robust. SUMIFS is heavily restricted by its identical-dimension rule, whereas FILTER easily evaluates rows across a broader matrix.

Excel & Data Problems