Combining SUMIFS with INDEX MATCH

You will sometimes want to retrieve data from a table with values both in rows and columns and based on multiple (three or more criteria). There is, as usual, more than one way to do this. However, most of those ways require you to use something called array formulas – in essence, you will have to press Ctrl Shift Enter combination on your keyboard every time you want to calculate that formula.

Combining SUMIFS with INDEX MATCH enables us to retrieve that data without such issues.

 

Let’s look at this table:

multi-dimensional table new

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 new

We’ve successfully retrieved our value:

multi-dimensional table sumifs return new

 

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

If we specify row number as 0, all of the rows, i.e. the whole column, will be returned:

multi-dimensional table index match new

This by itself doesn’t produce anything useful as we are returning the whole column into a single cell:

multi-dimensional table index match return new

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

multi-dimensional table sumifs index match new

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

multi-dimensional table sumifs index match return new

 

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

multi-dimensional table sumifs index match return 2 new

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

multi-dimensional table sumifs index match sum of multiple returns new

The basic principles shown here can be applied to other formulas as well, for example, you can combine AVERAGEIFS and INDEX MATCH.

All you have to do in order for your formulas to work properly is:

  • define your row number as zero in INDEX MATCH in order to return the whole column
  • make sure that your column is of the same size and in the same position as your criteria columns.

 

Dig deeper:

Conditional calculations

Lookup with unique identifiers

Leave a Reply

Your email address will not be published. Required fields are marked *