Combining SUMIFS with XLOOKUP

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria.

When SUMIFS is combined with XLOOKUP, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments.

By combining SUMIFS with XLOOKUP, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and resource-intensive array formulas.

 

Let’s look at this table:

multi-dimensional table

Let’s say we want to retrieve the value in the I6 column (marked orange).

With the SUMIFS formula, that is rather simple:

multi-dimensional table sumifs

We’ve successfully retrieved our value:

multi-dimensional table sumifs return

 

However, we don’t always want to retrieve data from just the Apr-20 column; we want to retrieve data from the specified column. Here is where the XLOOKUP function comes into play. Using XLOOKUP, we can retrieve the specified column, which we will later use as sum_range in the SUMIFS formula.

The XLOOKUP function is written in the following way:

multi-dimensional table xlookup

This XLOOKUP function will by itself produce only a spill into the cells below:

multi-dimensional table xlookup return

Only once we combine the formulas, i.e., the column returned by the XLOOKUP function is our sum_range in the SUMIFS formula, we can return our number for the specified column:

multi-dimensional table combined sumifs xlookup

We’ve now successfully retrieved our value from the specified column, for the specified criteria:

multi-dimensional table combined sumifs xlookup return

 

We can use the exact same formula to retrieve our value from any column that we want:

multi-dimensional table combined sumifs xlookup return

Naturally, if our criteria are matched in multiple rows, we will return the sum of those returns with the SUMIFS formula:

multi-dimensional table combined sumifs xlookup multiple returns sum

 

The basic principles shown here can be applied to other formulas as well; for example, you can combine COUNTIFS, AVERAGEIFS, MINIFS, and MAXIFS with the XLOOKUP function.

While writing formulas, in order to avoid errors, make sure that your sum criteria and sum range (lookup array and return array) are of the same size and in the same position. I.e., ensure that they are corresponding.

 

Are you looking for a way to sum values based on outside criteria? You can nest the XLOOKUP function as your SUMIFS criteria.

If you prefer INDEX MATCH to XLOOKUP, see how to combine SUMIFS with INDEX MATCH in order to sum all of the values that meet multiple criteria in different rows and columns.

Need even more power? See how to combine FILTER with XLOOKUP in order to return values based on criteria that are not present in the table we are returning values from.

 

Dig deeper:

XLOOKUP function

Combining SUMIFS with INDEX MATCH

Conditional calculations

Lookup with unique identifiers

Lookup with multiple criteria

5 thoughts on “Combining SUMIFS with XLOOKUP”

Leave a Reply