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)))**

**🔨 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 **INDEX**–**MATCH**functions 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)))**

**🔨 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)))**

**🔨 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))**

**🔨 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)))**

**🔨 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)))**

**🔨 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

**3 Examples to Use INDEX-MATCH Instead of VLOOKUP in Excel****Use INDEX-MATCH Across Multiple Sheets (3 Easy Ways)**