Sum with INDEX-MATCH Functions under Multiple Criteria in Excel

Dataset Overview

The screenshot below provides an overview of the article, showcasing a dataset and an example of how you can evaluate sums in Excel across different conditions using columns and rows.

1. SUM

• Objective: Sums all the numbers in a range of cells.
• Formula Syntax: =SUM(number1, [number2], …)

Example:

Suppose we have a dataset with a list of computer devices from different brands, along with their selling prices for six months in a computer shop. We want to find the total selling price of desktops for January only.

• In Cell F18, insert the following formula:

• Press Enter, and you’ll see the total selling price of all desktops for January.

Explanation:

• The SUM function uses only one array. (C5:C14=F16) instructs the function to match the criteria from cell F16 in the range of cells C5:C14. By adding another range of cells (D5:D14) with an asterisk (*) before it, we sum up all the values from that range under the given criteria.

2. INDEX

• Objective: Returns the value of a reference cell at the intersection of a specific row and column within a given range.
• Formula Syntax:
  =INDEX(array, row_num, [column_num])
  Or: =INDEX(reference, row_num, [column_num], [area_num])

• Example:

Let’s say we want to find the value at the intersection of the 3rd row and 4th column from the array of selling prices in the table.

• In cell F19, insert:
  =INDEX(D5:I14, 3, 4)
• Press Enter to get the result.

Explanation:

• The 4th column in the array represents the selling prices of all devices for April, and the 3rd row corresponds to the Lenovo Desktop category. So, at their intersection in the array, we’ll find the selling price of Lenovo Desktop in April.

3. MATCH

• Objective: Returns the relative position of an item in an array that matches a specified value in a specified order.
• Formula Syntax: =MATCH(lookup_value, lookup_array, [match_type])

Example:

Let’s determine the position of the month June from the month headers.

• In cell F17, insert the following formula:
  =MATCH(F16, D4:I4, 0)
• Press Enter to find that the column position of June is 6 in the month headers.

Change the month name in cell F17, and you’ll see the related column position for another selected month.

Additionally, if you want to know the row position of the brand Dell from the brand names in column B, insert the formula in cell F20:

=MATCH(F19, B5:B14, 0)

Here, B5:B14 is the range of cells where the brand name will be searched. If you change the brand name in cell F19, you’ll get the related row position for that brand from the selected range of cells.

Using INDEX and MATCH Functions Together in Excel

In this section, we’ll explore how to combine the INDEX and MATCH functions in Excel, along with examples. This combined function is effective for locating specific data within a large array. The MATCH function identifies the row and column positions of input values, while the INDEX function returns the output from the intersection of those positions.

Let’s consider our dataset, and our goal is to find the total selling price of the Lenovo brand in June.

Steps:

• In cell E19, insert the following formula:
  =INDEX(D5:I14, MATCH(E17, B5:B14, 0), MATCH(E16, D4:I4, 0))
• Press Enter to instantly get the result. If you change the month and device name in E16 and E17, respectively, the related result in E19 will update accordingly.

Nesting INDEX and MATCH Functions inside the SUM Function

Now let’s delve into the core part of the article, where we combine SUM (or SUMPRODUCT), INDEX, and MATCH functions. By using this compound function, we can obtain output data based on ten different criteria. While we’ll use the SUM function for all criteria, you can replace it with SUMPRODUCT if needed.

Criteria 1 – Finding Output Based on 1 Row and 1 Column

For our first criterion, let’s determine the total selling price of the Acer brand in April.

• In cell F20, insert the following formula:
  =SUM(INDEX(D5:I14, MATCH(F18, B5:B14, 0), MATCH(F19, D4:I4, 0)))
• Press Enter, and the return value will be $3,250.00.

Criteria 2 – Extracting Data Based on 1 Row and 2 Columns

Next, we want to find the total selling price of HP devices in both February and June.

• In cell F21, insert:
  =SUM(INDEX(D5:I14, MATCH(F18, B5:B14, 0), MATCH({“Feb”, “Jun”}, D4:I4, 0)))
• After pressing Enter, you’ll get the resultant value of $21,990.00.

In the second MATCH function, we define the months within curly brackets, which returns the column positions for both months. The INDEX function then searches for the selling prices based on the intersections of rows and columns, and the SUM function adds them up.

Criteria 3 – Determining Values Based on 1 Row and All Columns

Now let’s consider all columns with a fixed row. We’ll find the total selling price of Lenovo devices across all months.

• In cell F20, insert:
  =SUM(INDEX(D5:I14, MATCH(F18, B5:B14, 0), 0))
• Press Enter, and you’ll find the total selling price as $36,830.00.

In this function, we use 0 as the argument for column position inside the MATCH function to consider all columns.

Criteria 4 – Calculating Sum Based on 2 Rows and 1 Column

Finally, under the 2 rows and 1 column criteria, let’s find the total selling price of HP and Lenovo devices in June.

• In cell F21, insert the following formula:
  =SUM(INDEX(D5:I14, MATCH({“HP”, “Lenovo”}, B5:B14, 0), MATCH(F20, D4:I4, 0)))
• After pressing Enter, the return value will be $16,680.

Inside the first MATCH function, we enter HP and Lenovo within an array enclosed by curly braces.

Criteria 5 – Evaluating Sum Based on 2 Rows and 2 Columns with SUM, INDEX, and MATCH Functions Together

In this scenario, we’ll consider 2 rows and 2 columns to extract the total selling prices of HP and Lenovo devices for two specific months: April and June.

Steps:

• In cell F22, insert the following formula:
  
  =SUM(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH(F20,D4:I4,0)))+SUM(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH(F21,D4:I4,0)))
  
• Press Enter, and you’ll see the output as $25,980.00.

What we’re doing here is incorporating two SUM functions by adding a plus sign (+) between them for the two different months.

Criteria 6 – Finding Results Based on 2 Rows and All Columns with SUM, INDEX, and MATCH Functions Together

Now let’s deal with 2 rows and all columns. We’ll find out the total selling prices for HP and Lenovo devices across all months.

Steps:

• In cell F21, insert the following formula:
  
  =SUM(INDEX(D5:I14,MATCH(F18,B5:B14,0),0))+SUM(INDEX(D5:I14,MATCH(F19,B5:B14,0),0))
  
• Press Enter, and you’ll find the resultant value as $89,870.

Criteria 7 – Determining Output Based on All Rows and 1 Column with SUM, INDEX, and MATCH Functions Together

Under this criterion, we can extract the total selling prices of all devices for a single month (March).

Steps:

• In cell F20, insert the following formula:
  
  =SUM(INDEX(D5:I14,0,MATCH(F19,D4:I4,0)))
  
• Press Enter, and the return value will be $141,230.00.

Criteria 8 – Extracting Values Based on All Rows and 2 Columns with SUM, INDEX, and MATCH Functions Together

Now let’s determine the total selling price of all devices for two months: February and June.

Steps:

• In cell F21, insert the following formula:
  
  =SUM(INDEX(D5:I14,0,MATCH(F19,D4:I4,0)))+SUM(INDEX(D5:I14,0,MATCH(F20,D4:I4,0)))
  
• After pressing Enter, the total selling price will appear as $263,140.00.

Criteria 9 – Finding Results Based on All Rows and All Columns with SUM, INDEX, and MATCH Functions Together

Let’s find out the total selling price of all devices for all months in the table.

Steps:

• In cell F20, insert the following formula:
  
  =SUM(INDEX(D5:I14,0,0))
  
• Press Enter, and you’ll get the resultant value as $808,090.00.

You don’t need to use MATCH functions here, as we’re defining all column and row positions by typing 0 inside the INDEX function.

Criteria 10 – Calculating Sum Based on Distinct Pairs with SUM, INDEX, and MATCH Functions Together

In our final criterion, we’ll find out the total selling prices of HP devices for April along with Lenovo devices for June.

Steps:

• In cell F22, insert the following formula:
  
  =SUM(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH({"Apr","Jun"},D4:I4,0)))
  
• Press Enter, and you’ll see the result as $12,730.00.

When adding distinct pairs in this combined function, insert the device and month names inside the two arrays based on the arguments for row and column positions, ensuring that the device and month names from the pairs are maintained in corresponding order.

Using SUMIF with INDEX-MATCH Functions to Sum Under Multiple Criteria

Before diving into the applications of another combined formula, let’s first introduce the SUMIF function.

• Formula Objective:
  • The SUMIF function adds up the cells that meet specific conditions or criteria.

• Formula Syntax:
  • =SUMIF(range, criteria, [sum_range])

• Arguments:
  • range- Range of cells where the criteria lie.
  • criteria- Selected criteria for the range.
  • sum_range- Range of cells that are considered for summing up.

• Example:
  • Let’s use our previous dataset. Using the SUMIF function, we’ll find the total sales in May for desktops across all brands.
  • The formula in cell F18 will be:
    
    =SUMIF(C5:C14,F17,H5:H14)
    
  • After pressing Enter, you’ll get the total sales price as $71,810.

Now, let’s explore using SUMIF with INDEX & MATCH functions to sum under multiple criteria, considering both columns and rows. Our dataset has been slightly modified. In Column A, five brands are now present with multiple appearances for their two types of devices. The sales prices in the remaining columns remain unchanged.

Scenario:

• We want to find out the total sales of Lenovo devices in June.

Steps:

• In the output cell F18, the related formula will be:
  
  =SUMIF(B5:B14,F17,INDEX(D5:I14,0,MATCH(F16,D4:I4,0)))
  
• Press Enter, and you’ll get the total sales price for Lenovo in June.

If you want to switch to the device category (assuming you want to find the total sales price for desktops), the Sum Range will be C5:C14, and the Sum Criteria will be Desktop. In that case, the formula will be:

Using SUMIFS with INDEX & MATCH Functions in Excel

SUMIFS  is a subcategory of the SUMIF function. By combining SUMIFS with INDEX & MATCH functions, you can add more than one criterion, which is not possible with SUMIF alone. In SUMIFS, you enter the Sum Range first, followed by the Criteria Range and the range criteria.

Scenario:

• Let’s find out the sales price of Acer desktops in May. Along the rows, we’re adding two different criteria from Columns B & C.

Steps:

• The related formula in cell F19 will be:
  
  =SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)
  
• Press Enter, and the function will return $9,000.00.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel