Priti
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:
The SUM function adds individual values, cell references, ranges, or a combination of all three.
Syntax of the INDEX function:
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:
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.
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.
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–MATCHfunctions 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.
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
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
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.
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.
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.
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.
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.
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)
