Function Problems

How to Use SUMIFS Alternatives with Dynamic Arrays in Excel

Rana Garcia Oct 12, 2026 868 views

Question details

The user needs to combine code columns and calculate totals for unique codes generated by dynamic arrays, but encounters errors when using SUMIFS on dynamic source data.

How to Use SUMIFS Alternatives with Dynamic Arrays in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to sum values based on unique codes where the source ranges are generated dynamically by functions like FILTER or VSTACK.
Observed behavior: The standard SUMIFS function produces a #VALUE! error because it cannot process in-memory arrays generated by dynamic formulas as physical range arguments.

Before you start

Ensure you are using a version of Excel that supports Dynamic Arrays (such as Microsoft 365 or Excel 2021) and verify that your initial FILTER or VSTACK formula evaluates without errors before applying these alternatives.

Solution 1Recommended

Use the LET Function with REDUCE for Simple Dynamic Arrays

Best for straightforward dynamic array outputs. This method uses modern functions to extract unique codes and sum their totals directly in one spilled result.

Because SUMIFS strictly requires a physical worksheet range (like A2:A100), it will fail when handed a virtual array from a FILTER function. By combining LET, TOCOL, UNIQUE, and REDUCE, you can iterate through virtual arrays and perform conditional sums without referencing a hardcoded range.

Define the source data

Start your formula in the destination cell by typing =LET(. Define a variable for your source array, using TOCOL() if you need to stack multiple columns into a single vertical array.

Extract the unique codes

Within the LET function, assign a new variable (e.g., 'UnqCodes') to extract unique identifiers by applying the UNIQUE() and FILTER() functions to your stacked array.

Calculate dynamic totals

Use the REDUCE() function within the LET environment to iterate over 'UnqCodes'. Inside REDUCE, write a LAMBDA function that uses SUMIF to aggregate the corresponding values for each iterated code.

Spill the results

Structure the final output parameter of your LET function to output the unique codes side-by-side with their calculated sums. Press Enter to spill the complete dynamic table across the grid.

Use the LET Function with REDUCE for Simple Dynamic Arrays

Formula Organization: The LET function dramatically improves readability by allowing you to name intermediate calculations (like your stacked columns or unique lists) instead of writing one massive, nested formula.

Solution 2

Process Complex Data Using Power Query

Ideal when your dynamic source data frequently generates errors, contains multiple disconnected columns, or requires significant unpivoting before aggregation.

Advanced Formula Support

Master Dynamic Arrays Easily in WPS Spreadsheet

WPS Spreadsheet offers full native support for modern dynamic array functions including LET, UNIQUE, FILTER, and REDUCE. You can seamlessly calculate sums for dynamic arrays without worrying about legacy software limitations.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dynamic source data.
2. Enter the formula: Select the destination cell and input your dynamic array formula utilizing the LET and UNIQUE functions.
3. Spill the results: Press Enter. WPS Spreadsheet will automatically calculate and spill the dynamic totals across the appropriate adjacent cells.

100% compatible with complex Microsoft Excel formulas and .xlsx filesNative support for spilled dynamic arrays and modern functionsProcess complex calculations and large datasets quickly and efficientlyFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIFS return a #VALUE! error with dynamic arrays?

The SUMIFS function requires physical worksheet ranges (like A1:A10) for its criteria and sum range arguments. When you pass a virtual, in-memory array generated by functions like FILTER or VSTACK, SUMIFS cannot interpret it as a physical range, resulting in a #VALUE! error.

What is the benefit of using LET and REDUCE over standard SUMIFS?

The LET function allows you to define complex dynamic array outputs as easily readable variables. REDUCE can then iterate over these virtual arrays to apply conditional logic (like summing values per unique code), successfully bypassing the standard SUMIFS requirement for physical worksheet ranges.

When should I use Power Query instead of array formulas?

You should use Power Query when your source data is unstructured, requires heavy modification (such as unpivoting multiple columns), or frequently contains errors. Power Query transforms and aggregates the data behind the scenes, ensuring only clean, calculated totals are loaded into your workbook.

Excel & Data Problems