10 Cases to SUM with INDEX-MATCH for Multiple Criteria in Excel

Microsoft Excel is an excellent tool for calculation. In our day-to-day life, we frequently need to summate specific items from a vast dataset. Therefore, to perform this type of calculation, we can use the INDEX-MATCH function to look up the desired value and then add them using the SUM function. This article will demonstrate 10 unique cases to SUM with INDEX-MATCH for multiple criteria in Excel.


📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.


Overview of SUM, INDEX, and MATCH Functions

Details of the functions and their arguments:

Syntax of the SUM function:

SUM(number1,[number2],…)

The SUM function adds individual values, cell references, ranges, or a combination of all three.

Syntax of the INDEX function:

INDEX(array, row_num, [column_num])

The INDEX function extracts a value and returns it from a table, range, or reference to a value. This function has two forms array and reference. Here we will use the array form, and the arguments of the array form of the INDEX function are:

array– The array where you want to find the value.

row_num– The row number from which to return a value.

column_num-The column number from which to return a value. This input is needed if the row number is not selected only.

Syntax of the MATCH function:

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function looks for a specific item within a set of cells, finds it, and returns its position.

lookup_value– The lookup value is the value you want to match to find the desired value.

lookup_array-The cell range where to look for the desired value.

match_type– The match-type specifies how to match the lookup value with the lookup array. Here, 0 is to find the exact match, -1 is to find the smallest value greater than or equal to the lookup value, and 1 is to find the largest value less than or equal to the lookup value.


Learn to SUM with INDEX-MATCH for Multiple Criteria in Excel with These 10 Different Scenarios

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the Product Names and their prices for June to November. We will now calculate the sum for different products and months using SUM, INDEX, and MATCH functions. Hence, we will use this dataset to demonstrate how to SUM with INDEX-MATCH for multiple criteria in Excel with these 10 different scenarios.

Case 1

1. Using SUM, INDEX, and MATCH Functions to Find Output Based on 1 Row & 1 Column

This article will use SUM, INDEX, and MATCH functions with multiple criteria in Excel. In this first case, we will useSUM, INDEX, and MATCH functions to find the output from 1 row and 1 column. Here, we will find out the price of the iPad Pro for June, and let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want the output. We are selecting cell F13 and inserting the formula. We will get the price of the iPad Pro for the month of June.

=SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),MATCH(C14,C5:H5,0)))

Using SUM, INDEX and MATCH functions with multiple criteria in Excel

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),MATCH(C14,C5:H5,0)))

👉MATCH(C13,B6:B11,0), here, C13 contains the lookup value that we want to match within the lookup array B6:B11, and 0 says that we want an exact match. So, from this part, we will have 3 as output since iPad Pro is the third item in the range.

👉Similarly, MATCH(C14,C5:H5,0) will return 1 since June is the first item in the range.

👉 Then, the INDEX function looks up in the range C6:H11 and returns the value in the intersection of the third row and first column of the range.

👉 Lastly, the SUM function adds the value and displays the desired output.


Case 2

2. Extract Data Based on 1 Row & 2 Columns

In this second case, we will extract data from 1 row and 2 columns using the INDEXMATCHfunctions and then sum them using the SUM  function. We will calculate the sum of MacBook Air’s prices for July and August. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F13 and insert the formula. We will get the price of the iPad Pro for June.

=SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),MATCH({“July”,”August”},C5:H5,0)))

Using SUM, INDEX and MATCH functions with multiple criteria in Excel for 2 column and 1 row

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),MATCH({“July”,”August”},C5:H5,0)))

👉 MATCH(C13,B6:B11,0), here C13 contains the lookup value that we want to match within the lookup array B6:B11, and 0 says that we want an exact match. So, from this part, we will have 1 as output since MacBook Air is the first item in the range.

👉 Similarly, MATCH({“July”,”August”},C5:H5,0) will look up the exact match for “July” and “August” in the array C5:H5 and return 2 and 3.

👉 Then, the INDEX function looks up in the range C6:H11 and returns the values in the intersection of the first row, second and third columns of the range.

👉 Lastly, the SUM function adds the values and displays the desired output.


Case 3

3. Determine Values Based on 1 Row & All Columns

This third case will calculate the total price of AirPods for all months, which means looking for values from 1 row and all columns. Now, we will use SUM, INDEX, and MATCH functions to find the output from 1 row and all columns. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F13 and insert the formula. We will get the price of the iPad Pro for June.

=SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),0))

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),0))

👉 MATCH(C13,B6:B11,0), here C13 contains the lookup value that we want to match within the lookup array B6:B11, and 0 says that we want an exact match. So, from this part, we will have 5 as output since AirPods is the fifth item in the range.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match function 5 denotes the row reference and 0 denotes all columns. So, this function returns the values of the fifth row of all columns.

👉 Lastly, the SUM function adds the values returned from the INDEX function and displays the desired output.

📕 Read More: Use INDEX Function to Match and Return Multiple Values Vertically


Case 4

4. Calculate Sum Based on 2 Rows & 1 Column

Now, we will calculate the sum of prices from 2 rows, iPad Pro and AirPods, for 1 column, the month of June and we will use  SUM, INDEX, and MATCH functions to find the output. Now let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F13 and insert the formula. We will get the price of the iPad Pro for June.

=SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH(C15,C5:H5,0)))

Using SUM, INDEX and MATCH functions with multiple criteria in Excel for 2 rows and 1 column

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH(C15,C5:H5,0)))

👉 MATCH(C15,C5:H5,0), here, C15 contains the lookup value that we want to match within the lookup array C5:H5, and 0 says that we want an exact match. So, from this part, we will have 1 as output since June is the first item in the range.

👉 Similarly, MATCH({“iPad Pro”,”AirPods”},B6:B11,0) looks for the exact match for “iPad Pro” and “AirPods” in the array B6:B11 and returns 3 and 5.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match functions 3 and 5 denotes the row references, and 1 denotes the first column. So, this function returns the values of the third and fifth rows of the first column.

👉 Lastly, the SUM function adds the values returned from the INDEX function and displays the desired output.

📕 Read More: 6 Methods to Match Multiple Criteria from Different Arrays in Excel


Case 5

5. Evaluate Sum Based on 2 Rows & 2 Columns

We will utilize the SUM, INDEX, and MATCH functions to evaluate the sum from the rows of iPad Pro and AirPods for June and July. Now, follow these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Insert the formula given below in cell F13. Therefore, we will get the sum of the prices of the iPad Pro and AirPods for June and July.

=SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH(C15,C5:H5,0)))+SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH(C16,C5:H5,0)))

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH(C15,C5:H5,0)))+SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH(C16,C5:H5,0)))

👉 MATCH(C15,C5:H5,0), here, C15 contains the lookup value that we want to match within the lookup array C5:H5, and 0 says that we want an exact match. So, from this part, we will have 1 as output since June is the first item in the range.

👉 Similarly, MATCH({“iPad Pro”,”AirPods”},B6:B11,0) looks for the exact match for “iPad Pro” and “AirPods” in the array B6:B11 and returns 3 and 5.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match functions 3 and 5 denotes the row references, and 1 denotes the first column. So, this function returns the values of the third and fifth rows of the first column.

👉 Now, the SUM function adds the values returned from the INDEX function and we have the sum of June for the two products.

👉Lastly, the second part of addition sums the price for July of the two products similarly and then adds up with the previous value and displays the result.


Case 6

6. Find Out Data Based on 2 Rows & All Columns

In this case, we will find out the prices of the Apple Watch and iPhone 14 Pro for all months from June to November and we will use  SUM, INDEX, and MATCH to do that. Now, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F13 and insert the formula. Hence, we will get the prices of the Apple Watch and iPhone 14 Pro for all months from June to November.

=SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),0))+SUM(INDEX(C6:H11,MATCH(C14,B6:B11,0),0))

Using SUM, INDEX and MATCH functions with multiple criteria in Excel for 2 rows and all column

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH(C13,B6:B11,0),0))+SUM(INDEX(C6:H11,MATCH(C14,B6:B11,0),0))

👉 This formula is like the previous case. The two parts work similarly. So, we are describing only the first part.

👉 MATCH(C13,B6:B11,0), here, C13 contains the lookup value that we want to match within the lookup array B6:B11, and 0 says that we want an exact match. So, from this part, we will have 4 as output since Apple Watch is the fourth item in the range.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match functions 4 denotes the row reference, and 0 denotes all the columns. So, this function returns the values of the fourth row of all the columns.

👉 Lastly, the SUM function adds the values returned from the INDEX function.


Case 7

7. Determine Output Based on All Rows & 1 Column

Now, we will use  SUM, INDEX, and MATCH to determine the output from November for all the products. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell F13. Then, insert the formula. Therefore, we will have our output in cell F13.

=SUM(INDEX(C6:H11,0,MATCH(C14,C5:H5,0)))

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

=SUM(INDEX(C6:H11,0,MATCH(C14,C5:H5,0)))

👉 MATCH(C14,C5:H5,0), here, C14 contains the lookup value that we want to match within the lookup array C5:H5, and 0 says that we want an exact match. So, from this part, we will have 6 as output since November is the sixth item in the range.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match functions 6 denotes the row reference, and 0 denotes all the columns. So, this function returns the values of the sixth row of all the columns.

👉 Lastly, the SUM function adds the values returned from the INDEX function.


Case 8

8. Extract Values Based on All Rows & 2 Columns

At this point, we will sum up the prices for August and September for all the products by combining SUM, INDEX, and MATCH functions. See the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Insert the formula after selecting cell F13. Hence, we will get the sum of all products for August and September in cell F13.

=SUM(INDEX(C6:H11,0,MATCH(C14,C5:H5,0)))+SUM(INDEX(C6:H11,0,MATCH(C15,C5:H5,0)))

Using SUM, INDEX and MATCH functions with multiple criteria in Excel for all rows and two columns

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,0,MATCH(C14,C5:H5,0)))+SUM(INDEX(C6:H11,0,MATCH(C15,C5:H5,0)))

👉 MATCH(C14,C5:H5,0), here, C14 contains the lookup value that we want to match within the lookup array C5:H5, and 0 says that we want an exact match. So, from this part, we will have 3 as output since August is the third item in the range.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match functions 3 denotes the row reference, and 0 denotes all the columns. So, this function returns the values of the third row of all the columns.

👉 Lastly, the SUM function adds the values returned from the INDEX function.


Case 9

9. Find Out Results Based on All Rows & All Columns

In this case, we will calculate the sum for all the rows and columns by using SUM, INDEX, and MATCH functions. Follow these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell F13. Then, insert the formula and we will get the sum of all products from June to November.

=SUM(INDEX(C6:H11,0,0))

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,0,0))

👉 Here, the INDEX function looks up in the range C6:H11; the first 0 denotes all rows, and the second 0 denotes all the columns. So, this function returns the values of all rows and columns.

👉 Lastly, the SUM function adds the values returned from the INDEX function and returns the result.


Case 10

10. Calculate Sum Based on Distinct Pairs

Lastly, we will demonstrate the formula to sum up any pair of products or months. Now, we will use SUM, INDEX, and MATCH functions for this criterion in Excel. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell F13 and insert the formula. We will get the sum.

=SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH({“October”,”November”},C5:H5,0)))

Using SUM, INDEX and MATCH functions with multiple criteria in Excel for distinct pairs

🔨 Formula Breakdown

Here, the formula we inserted in cell F13 is:

SUM(INDEX(C6:H11,MATCH({“iPad Pro”,”AirPods”},B6:B11,0),MATCH({“October”,”November”},C5:H5,0)))

👉 MATCH({“iPad Pro”,”AirPods”},B6:B11,0) looks for the exact match for “iPad Pro” and “AirPods” in the array B6:B11 and returns 3 and 5.

👉 Similarly, MATCH({“October”,”November”},C5:H5,0) looks for the exact match for “October” and “November” in the array C5:H5 and returns 5 and 6.

👉 Then, the INDEX function looks up in the range C6:H11, and output from match functions 3 and 5 denotes the row references, and 5,6 denotes the column references. So, this function returns the values of the intersection of the third row, fifth column, and the fifth row, sixth column.

👉 Lastly, the SUM function adds the values returned from the INDEX function and displays the result.


📄 Important Notes

🖊️  Here, we have used an exact match for the MATCH function. If the value does not match exactly, it will show #N/A error. So, make sure that the values match precisely.

🖊️  In the INDEX function, if we keep the column number empty or enter 0, it will return the value from all the columns.


📝 Takeaway from This Article

📌  This article demonstrated 10 Unique Cases to SUM with INDEX-MATCH for Multiple Criteria in Excel.

📌   In brief, we have used SUM, INDEX, and MATCH functions for multiple criteria.


Conclusion

This article has demonstrated 10 Unique Cases to SUM with INDEX-MATCH for Multiple Criteria in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.


Related Articles

(Visited 148 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo