Function Problems

How to Create a Fully Dynamic SUMIF Formula for Excel Arrays

Huda Qurayshi Oct 1, 2026 868 views

Question details

The user needs a scalable formula to sum values based on unique criteria (such as temperature) per row, which adapts automatically to changing row, column, and criteria counts without manual updates.

How to Create a Fully Dynamic SUMIF Formula for Excel Arrays

Product: Spreadsheet
Device & OS: not provided
Scenario: Grouping and summing data by unique criteria dynamically across multiple rows and columns while preserving the source row structure.
Observed behavior: Standard formulas require manual adjustments when data dimensions or unique criteria change, preventing the output from expanding automatically.

Before you start

Verify that your spreadsheet software supports modern dynamic array functions such as UNIQUE, BYROW, HSTACK, and VSTACK, as these are required for the formula to spill automatically.

Solution 1Recommended

Use UNIQUE, BYROW, and SUMIF for a Fully Dynamic Array

Combine advanced dynamic array functions to extract unique headers and calculate row-by-row totals that automatically scale with your data.

By utilizing dynamic array functions, you can avoid manually referencing every single unique criteria. The UNIQUE function extracts the varying criteria (like temperatures), while BYROW ensures the SUMIF logic is evaluated individually for every single row in your dataset.

When these functions are nested together with VSTACK or HSTACK, the final output acts as a single, cohesive array that expands or shrinks based on the source data dimensions.

Extract unique criteria

Identify the row or column containing your criteria (e.g., temperatures) and use =TRANSPOSE(UNIQUE(criteria_range)) to generate a dynamic list of headers across columns.

Set up the BYROW function

Use the BYROW function to iterate through your main source data line by line by typing =BYROW(source_data_range, LAMBDA(row, ...)).

Apply SUMIFS inside the LAMBDA

Within the LAMBDA function, insert your SUMIFS formula to sum the values in the current 'row' that match the spilled unique criteria array you created in step 1.

Stack the results

Wrap your functions using VSTACK to stack the dynamic headers on top of the BYROW calculation results, creating a single formula that outputs the entire table.

Use UNIQUE, BYROW, and SUMIF for a Fully Dynamic Array

Automatic Scaling: Because the criteria array and the row iterations are dynamic, adding new data rows or entirely new temperatures will cause the result array to update and spill automatically.

Master Dynamic Arrays with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for modern dynamic array functions, making it simple to build scalable, robust formulas that automatically adapt to your data changes.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your source data.
2. Select the destination: Click on the top-left cell where you want the dynamic array results to begin spilling.
3. Enter the dynamic formula: Type your combined UNIQUE, BYROW, and SUMIF formula into the formula bar.
4. Execute and spill: Press Enter. WPS Spreadsheet will automatically calculate and spill the results into the adjacent rows and columns.

Full support for advanced functions like UNIQUE, BYROW, HSTACK, and VSTACK.High compatibility with Microsoft Excel formulas and array formatting.Fast computation engine handles large, complex dynamic datasets smoothly.Intuitive formula builder and syntax highlighting to prevent errors.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my dynamic array formula returning a #SPILL! error?

A #SPILL! error occurs when the spreadsheet attempts to expand the dynamic array results, but one or more cells in the required spill area are not empty. Clearing the blocking data from the destination cells will instantly resolve this error.

Can I use this dynamic array setup in older versions of Excel?

No. Functions like UNIQUE, BYROW, and LAMBDA are only available in newer versions (like Microsoft 365, Excel 2021, or recent updates of WPS Office). Older versions require legacy array formulas (Ctrl+Shift+Enter) which cannot resize automatically.

How do I add multiple conditions to this dynamic grouping formula?

You can replace SUMIF with SUMIFS inside your LAMBDA function. This allows you to add as many criteria ranges and conditions as needed while keeping the overall array fully dynamic.

Excel & Data Problems