In MS Excel, there is a huge diversity of using SUMPRODUCT with INDEX and MATCH functions together. In this article, I’ll try to illustrate how we can use this compound function effectively under a number of criteria along rows & columns.
Introduction to the Functions: SUMPRODUCT, INDEX and MATCH with Examples
Before getting down to how these three powerful functions work combinedly, let’s get introduced to these functions & their working process one by one.
1. SUMPRODUCT Function
- Syntax:
=SUMPRODUCT(array1,[array2],[array3],…)
- Function:
Returns the sum of the products of corresponding ranges or arrays.
- Example:
In our dataset, a list of computer devices of different brands is present along with the selling prices of 6 months for a computer shop. We want to know the total selling price of the desktops of all brands for January only.
📌 Steps:
➤ Firstly, in Cell F18, we have to type:
=SUMPRODUCT((C5:C14=F16)*D5:D14)
Here, the range of cells C5:C14 indicates the cells of the Device Category column, cell F16 refers to the selected Device and the range of cells D5:D14 represents the cells of the Jan column.
➤ After that, press ENTER & you’ll see the total selling price of all desktops for January at once.
Inside the SUMPRODUCT function, there lies only one array. Here, C5:C14=F16 means we’re instructing the function to match criteria from Cell F16 in the range of cells C5:C14. By adding another range of cells D5:D14 with an Asterisk(*) before, we’re telling the function to sum up all the values from that range under the given criteria.
2. INDEX Function
- Syntax:
=INDEX(array, row_num, [column_num])
or,
=INDEX(reference, row_num, [column_num], [area_num])
- Function:
Returns a value of reference of the cell at the intersection of the particular row and column, in a given range.
- Example:
Assuming that we want to know the value at the intersection of the 3rd row & 4th column from the array of selling prices from the table.
📌 Steps:
➤ In Cell F19, type:
=INDEX(D5:I14,3,4)
➤ Press ENTER & you’ll get the result.
Since the 4th column in the array represents the selling prices of all devices for April & the 3rd row represents the Lenovo Desktop Category, at their intersection in the array, we’ll find the selling price of Lenovo desktop in April.
3. MATCH Function
- Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
- Function:
Returns the relative position of an item in an array that matches a specified value in a specified order.
- Example:
First of all, we’re going to know the position of the month of June from the month headers.
📌 Steps:
➤ In Cell F17, our formula will be:
=MATCH(F16,D4:I4,0)
➤ Press ENTER & you’ll find that the column position of the month of June is 6 in the month headers.
Change the name of the month in Cell F17 & you’ll see the related column position of another month selected.
And if we want to know the row position of the brand Dell from the names of the brands in Column B, then the formula in Cell F20 will be:
=MATCH(F19,B5:B14,0)
Here, B5:B14 is the range of cells where the name of the brand will be looked for. If you change the brand name in Cell F19, you’ll get the related row position of that brand from the selected range of cells.
Use of INDEX and MATCH Functions Together in Excel
Now we’ll know how to use INDEX & MATCH functions together as a function and what exactly this combined function returns as output. This combined INDEX-MATCH function is really effective to find specific data from a large array. MATCH function here looks for the row & column positions of the input values & the INDEX function will simply return the output from the intersection of that row & column positions.
Now, based on our dataset, we want to know the total selling price of the Lenovo brand in June.
📌 Steps:
➤Firstly, in cell E19, type:
=INDEX(D5:I14,MATCH(E17,B5:B14,0),MATCH(E16,D4:I4,0))
Here, cell E17 refers to the selected Device, the range of cells B5:B14 indicates the cells of the Brand column, and cell E16 represents the selected Month.
Formula Breakdown
- MATCH(E16,D4:I4,0)
- E16 → It is the lookpu_value argument.
- D4:I4 → This refers to the lookup_array argument.
- 0 → It indicates the [match_type] argument.
- Output → 6.
- MATCH(E17,B5:B14,0)
- Output → 3.
- INDEX(D5:I14,MATCH(E17,B5:B14,0),MATCH(E16,D4:I4,0)) → It becomes INDEX(D5:I14,3,6).
- D5:I14 → It is the array argument.
- 3 → This represents the row_num argument.
- 6 → It refers to the [column_num] argument.
- Output → $6,580.
➤ Now, press ENTER & you’ll find the result instantly.
If you change the month & device name in E16 & E17 respectively, you’ll get the related result in E19 at once.
Read More: Excel INDEX MATCH Example
SUMPRODUCT with INDEX and MATCH Functions in Excel
Here’s the main & final part of the article based on the uses of SUMPRODUCT, INDEX & MATCH functions together. We can find the output data under 10 different criteria by using this compound function.
Criteria 1: Finding Output Based on 1 Row & 1 ColumnÂ
Based on our 1st criterion, we want to know the total selling price of the Acer brand in the month of April.
📌 Steps:
➤ Firstly, in cell F20, the formula will be:
=SUMPRODUCT(INDEX(D5:I14,MATCH(F18,B5:B14,0),MATCH(F19,D4:I4,0)))
Here, cell F18 indicates the selected Device, and cell F19 represents the selected Month.
Formula Breakdown
- Â Here, the 1st and the 2nd MATCH function returns the row_num and the [column_num] arguments for the INDEX function.
- Then, the INDEX function returns an array that enters into the SUMPRODUCT function.
- Finally, the SUMPRODUCT function returns the output $3,250.
➤ After that, press ENTER & the return value will be $3,250.
Read More: INDEX MATCH with 3 Criteria in Excel
Criteria 2: Extracting Data Based on 1 Row & 2 ColumnsÂ
Now we want to know the total selling price of HP devices in the months of February as well as June.
📌 Steps:
➤ In Cell F21, we have to type:
=SUMPRODUCT(INDEX(D5:I14,MATCH(F18,B5:B14,0),MATCH({"Feb","Jun"},D4:I4,0)))
Here, cell F18 refers to the selected Device.
Formula Breakdown
- Here, in the second MATCH function, we’re defining the months within curly brackets. It’ll return the column positions of both of the months.
- Output → {2,6}.
- INDEX function then searches for the selling prices based on the intersections of rows & columns.
- Finally, the SUMPRODUCT function will add them up.
- Output → $21,990.
➤ After pressing ENTER, you’ll find the resultant value as $21,990.
Read More: INDEX, MATCH and MAX with Multiple Criteria in Excel
Criteria 3: Determining Values Based on 1 Row & All ColumnsÂ
In this part, we’ll deal with all columns with 1 fixed row. So, we can find the total selling price of Lenovo devices in all months under our criteria here.
📌 Steps:
➤ In Cell F20, type:
=SUMPRODUCT(INDEX(D5:I14,MATCH(F18,B5:B14,0),0))
Formula Breakdown
- Here, the MATCH function returns the row_num of the selected Device.
- Output → 3.
- INDEX function then searches for the selling prices based on the intersections of rows & columns.
- Output → {6500,7650,4250,3150,8700,6580}.
- Finally, the SUMPRODUCT function will add them up.
- Output → $36,830.
➤ Press ENTER & you’ll find the total selling price as $36,830.
In this function, to add criteria for considering all months or all columns, we have to type 0 as the argument- column_pos inside the MATCH function.
Similar Readings
- INDEX MATCH Formula with Multiple Criteria in Different Sheet
- SUMIF with INDEX and MATCH Functions in Excel
- INDEX-MATCH with Multiple Matches in Excel
- Use INDEX MATCH for Multiple Criteria Without Array
- Excel INDEX MATCH with Multiple Criteria and Multiple Results
Criteria 4: Calculating Sum Based on 2 Rows & 1 ColumnÂ
In this section under 2 rows & 1 column criteria, we’ll find out the total selling price of HP & Lenovo devices in the month of June.
📌 Steps:
➤ In Cell F21, the formula will be under the given criteria:
=SUMPRODUCT(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH(F20,D4:I4,0)))
Hare, cell F20 represents the selected Month.
Formula Breakdown
- Here, the 1st MATCH function returns the row_num of the selected Devices.
- Output → {1,3}.
- Then, the 2nd MATCH function returns the column_num of the selected Month.
- Output → 6.
- INDEX function then searches for the selling prices based on the intersections of rows & columns.
- Finally, the SUMPRODUCT function will add them up.
- Output → $16,680.
➤ After pressing ENTER, we’ll find the return value as $16,680.
Here inside the first MATCH function, we have to input HP & Lenovo inside an array by enclosing them with curly brackets.
Read More: Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
Criteria 5: Evaluating Sum Based on 2 Rows & 2 ColumnsÂ
Now we’ll consider 2 rows & 2 columns to extract the total selling prices of HP & Lenovo devices for two particular months- April & June.
📌 Steps:
➤ Type in Cell F22:
=SUMPRODUCT(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH(F20,D4:I4,0)))+SUMPRODUCT(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH(F21,D4:I4,0)))
What we’re doing here is incorporating two SUMPRODUCT functions by adding a Plus(+) between them for two different months.
➤ Press ENTER & you’ll see the output as $25,980.
Read More: How to Sum Multiple Rows Using INDEX MATCH Formula
Criteria 6: Finding out Result Based on 2 Rows & All ColumnsÂ
In this part, let’s deal with 2 rows & all columns. So we’ll find out the total selling prices for HP & Lenovo devices in all months.
📌 Steps:
➤ Our formula will be in Cell F21:
=SUMPRODUCT(INDEX(D5:I14,MATCH(F18,B5:B14,0),0))+SUMPRODUCT(INDEX(D5:I14,MATCH(F19,B5:B14,0),0))
Like in the previous method, we are incorporating two SUMPRODUCT functions by adding a Plus(+) between them for 2 different Devices for all months.
➤ Press ENTER & we’ll find the resultant value as $89,870.
Read More: INDEX MATCH for Multiple Criteria in Rows and Columns in Excel
Criteria 7: Determining Output Based on All Rows & 1 ColumnÂ
Under this criterion, we can now extract the total selling prices of all Devices for a single month (March).
📌 Steps:
➤ Insert the formula in Cell F20:
=SUMPRODUCT(INDEX(D5:I14,0,MATCH(F19,D4:I4,0)))
Formula Breakdown
- Here, the MATCH function returns the column_num of the selected Month.
- Output → 3.
- INDEX function then searches for the selling prices based on the intersections of rows & columns.
- Output → {7560;14260;4250;12870;8110;21360;27890;9250;16000;19680}.
- Finally, the SUMPRODUCT function will add them up.
- Output → $141,230.
➤ Press ENTER & you’re done. The return value will be $141,230.
Read More: How to Use INDEX MATCH Instead of VLOOKUP in Excel
Criteria 8: Extracting Values Based on All Rows & 2 ColumnsÂ
In this part, we’ll determine the total selling price of all devices for two months- February & June.
📌 Steps:
➤ In Cell F21, we have to type:
=SUMPRODUCT(INDEX(D5:I14,0,MATCH(F19,D4:I4,0)))+SUMPRODUCT(INDEX(D5:I14,0,MATCH(F20,D4:I4,0)))
Here, we are applying two SUMPRODUCT functions by adding a Plus(+) between them for 2 different Months for all Devices.
➤ After pressing ENTER, the total selling price will appear as $263,140.
Criteria 9: Finding Result Based on All Rows & All ColumnsÂ
We’ll now find out the total selling price of all Devices for all months in the table.
📌 Steps:
➤ In Cell F20, you have to type:
=SUMPRODUCT(INDEX(D5:I14,0,0))
➤ Press ENTER & you’ll get the resultant value as $808,090.
You don’t need to use MATCH functions here as we’re defining all columns & row positions by typing 0’s inside the INDEX function.
Similar Readings
- INDEX-MATCH Formula to Find Minimum Value in Excel
- How to Use INDIRECT, INDEX, and MATCH Functions in Excel
- INDEX MATCH across Multiple Sheets in Excel
- How to Use IF with INDEX & MATCH Functions in Excel
- Excel INDEX MATCH If Cell Contains Text
- XLOOKUP vs INDEX-MATCH in Excel
Criteria 10: Calculating Sum Based on Distinct PairsÂ
In our final criterion, we’ll find out the total selling prices of HP devices for April along with Lenovo devices for June together.
📌 Steps:
➤ Under this criterion, our formula in Cell F22 will be:
=SUMPRODUCT(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH({"Apr","Jun"},D4:I4,0)))
Formula Breakdown
- Here, the 1st MATCH function returns the row_num of the selected Devices of the 2 Pairs.
- Output → {1,3}.
- Then, the 2nd MATCH function returns the column_num of the selected Months of the 2 Pairs.
- Output → {4,6}.
- INDEX function then searches for the selling prices based on the intersections of rows & columns.
- Finally, the SUMPRODUCT function will add them up.
- Output → $12,730.
➤ Now press ENTER & you’ll see the result as $12,730.
While adding distinct pairs in this combined function, we have to insert the Device & Month names inside the two arrays based on the arguments for row & column positions and the Device & Month names from the pairs must be maintained in corresponding order.
Read More:Â How to Match Multiple Criteria from Different Arrays in Excel
SUMPRODUCT vs INDEX-MATCH
- The SUMPRODUCT function returns the sum of the products of selected arrays. It can be used as an alternative to array formulas. SUMPRODUCT function can also be used with multiple criteria in Excel for various analysis and comparisons.
- On the other hand, the combination of INDEX and MATCH functions can be an quite efficient alternative to the lookup functions of Excel to search for a specific value within a specified dataset. A combination of the SUMIFS function with the INDEX-MATCH functions can do wonders while calculating a conditional sum for multiple criteria.
Download Practice Workbook
You can download the Excel Workbook that we’ve used to prepared this article. You can modify the data & see new results.
Concluding Words
I hope this article on the uses of SUMPRODUCT, INDEX & MATCH functions together will now prompt you to apply in your regular Excel works. If you got any questions or feedback please let me know through your valuable comments. You can also have a glance at our other informative & interesting articles based on Excel functions on this website.
Related Articles
- INDEX MATCH Multiple Criteria with Wildcard in Excel
- How to Use INDEX MATCH with Multiple Criteria in Excel
- INDEX-MATCH with Duplicate Values in Excel
- Excel INDEX MATCH to Return Multiple Values in One Cell
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel
- How to Use INDEX-MATCH Function for Multiple Results in Excel
- How to Use INDEX MATCH with Multiple Criteria for Date Range
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
- Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
- INDEX Function to Match & Return Multiple Values Vertically in Excel
- How to Use Excel VBA INDEX MATCH with Array
- VBA INDEX MATCH Based on Multiple Criteria in Excel
May u help me to find a formula to calculate a cost of sales for products a according to fixed unit rate with monthly fluctuations and daily sales qtys.
Hello Ahmed, you can go through this article-
https://www.exceldemy.com/calculate-cost-per-unit-in-excel/
I hope, you will find your solution. Let us know the outcome by leaving a comment. Thank you!
Send your problem to this email: [email protected]
hello sir hope you will be fine i appreciate your work
dear i Noor form pakistan and i have a problem with monthly paid amount dear sir i want to show me that wich month a student paid for example i have id number and amount paid are in row like that
Id Amount Month
100 2000 JAN
102 1000 Jan
103 4000 jan
100 2000 Mar
Respected sir this an example of my sheet
so i want to give an id in cell and show me amount paid with month
and also describe that if an id paid twis in a month so how to treat
thanks
hello, you can use the FILTER function to do this. Suppose, you have inserted your dataset into the range B2:D6.
And, in cell G2. you have inserted the id for what you want to get month and payment.
so, to get the required data, you have to insert the following formula into any blank cell:
Use this formula for your dataset and let us know the outcome in the reply. Thank you!