The two ranges are “non-contiguous”, which means they are not connected or touching.

Both ranges contain a list of colors in the first column and quantities in the second column.

Let’s walk through some options step by step.

Using SUMIFS twice in the same formula

Using the SUM function

TheSUM functioncan handle non-contiguous ranges natively.

However, things get more complicated if we want to perform a conditional sum.

This would normally be a job for the SUMIF or SUMIFS function.

Using SUMIFS twice in the same formula

Thecriteria(H5) is a relative address because we want this to change.

The values in the range H5:H14 have been entered manually.

How can we avoid this problem?

Using SUMIFS twice in the same formula

In the latest version of Excel, the VSTACK function provides a nice way to simplify things.

Note: One way to make things easier would be to define each range as anExcel Table.

This would allow us to usestructured referencesinstead of absolute references and make the formula easier to enter and read.

Using SUMIFS twice in the same formula

Plus, using Excel Tables would make each range dynamic so that they will expand to include new data.

you’re free to see this approach in the screen below.

However, we still need to maintain the colors listed in column K manually.

Using SUMIFS twice in the same formula

How can we further streamline this process?

We can do this by combining VSTACK with the GROUPBY function.

We can’t feed the result from VSTACK directly into SUMIFS, unfortunately.

Using VSTACK to combine the two ranges

The values argument contains values that will be aggregated by the function specified (quantities in this example).

Last, thefunctionspecifies the calculation to run (SUM in this case).

We do this to make the formula efficient and easy to read.

Using VSTACK to combine ranges and SUMIFS on the result

We have already combined the ranges asdata.

The second column (quantities) is delivered as thevaluesargument.

Then, for thefunctionargument, we provide SUM because we want to sum quantities by color.

Using VSTACK with GROUPBY (all in one formula)

This table is dynamic.

If color names change, the table will automatically update.

If we add more ranges to VSTACK, everything will continue to work properly.

Excel formula: Combine data in multiple worksheets

The result is similar to a Pivot Table, but there is no need to refresh the table manually.

Note: The Total row is created automatically.

This can be disabled by setting total_depth to zero (0).

Excel formula: Combine ranges

LET Function

The Excel LET function lets you define named variables in a formula.

The result is a summary table created with a single formula.

CHOOSECOLS Function

The Excel CHOOSECOLS function returns specific columns from an array or range.

Excel SUMIFS function

The columns to return are provided as numbers in separate arguments.

Each number corresponds to the numeric index of a column in the given array.

Excel LET function

Excel VSTACK function

Excel GROUPBY function

Excel CHOOSECOLS function

Using SUMIFS twice in the same formula

Using VSTACK to combine the two ranges

Using VSTACK to combine ranges and SUMIFS on the result

Using VSTACK with GROUPBY (all in one formula)