While working with a large amount of data in Excel, it’s very common to use **INDEX-MATCH** functions to lookup parameters under multiple criteria for sum or other related applications. In this article, you’ll get to know how you can incorporate **SUM**, **SUMPRODUCT**, **SUMIF**, or **SUMIFS **functions along with the **INDEX-MATCH **formula to sum or evaluate summation under numerous criteria in Excel.

The above screenshot is an overview of the article which represents a dataset & an example of how you can evaluate sum in Excel under different conditions along with columns & rows. You’ll learn more about the dataset and all suitable functions in the following methods in this article.

**Introduction to the Functions: SUM, INDEX and MATCH with Examples**

Before getting down to how these three functions work combinedly, let’s get introduced to these functions & their working process one by one.

**1. SUM**

**Objective:**

**Sums all the numbers in a range of cells.**

**Formula Syntax:**

**=SUM(number1, [number2],…)**

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

➤ In** Cell F18**, we have to type:

`=SUM((C5:C14=F16)*D5:D14)`

➤ Press **Enter** & you’ll see the total selling price of all desktops for January at once.

Inside **the SUM 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**

**Objective:**

**Returns a value of reference of the cell at the intersection of the particular row and column, in a given range.**

**Formula Syntax:**

**=INDEX(array, row_num, [column_num])**

**or, **

**=INDEX(reference, row_num, [column_num], [area_num])**

**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, 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:**

First of all, we’re going to know the position of the month 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 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 with examples as a function and what exactly this combined function returns as output. This combined **INDEX-MATCH** function is effective to find specific data from a large array. **The 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:**

➤ In **Cell E19**, type:

`=INDEX(D5:I14,MATCH(E17,B5:B14,0),MATCH(E16,D4:I4,0))`

➤ 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.

**Nesting INDEX and MATCH Functions inside the SUM Function**

Here’s the core part of the article based on the uses of **SUM** or **SUMPRODUCT, INDEX & MATCH** functions together. We can find the output data under 10 different criteria by using this compound function. Here, the **SUM** function will be used for all of our criteria but you can replace it with the **SUMPRODUCT** function too & the results will be unchanged.

**Criteria 1: Finding Output Based on 1 Row & 1 Column with SUM, INDEX and MATCH Functions Together**

Based on our 1st criterion, we want to know the total selling price of the Acer brand in April.

**📌**** Steps:**

➤ In **Cell F20**, the formula will be:

`=SUM(INDEX(D5:I14,MATCH(F18,B5:B14,0),MATCH(F19,D4:I4,0)))`

➤ Press **Enter **& the return value will be $3,250.00.

**Criteria 2: Extracting Data Based on 1 Row & 2 Columns with SUM, INDEX and MATCH Functions Together**

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:

`=SUM(INDEX(D5:I14,MATCH(F18,B5:B14,0),MATCH({"Feb","Jun"},D4:I4,0)))`

➤ After pressing **Enter**, you’ll find the resultant value as $21,990.00.

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.** INDEX** function then searches for the selling prices based on the intersections of rows & columns and finally **SUM** function will add them up.

**Criteria 3: Determining Values Based on 1 Row & All Columns with SUM, INDEX and MATCH Functions Together**

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:

`=SUM(INDEX(D5:I14,MATCH(F18,B5:B14,0),0))`

➤ Press **Enter **& you’ll find the total selling price as $36,830.00.

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.

**Criteria 4: Calculating Sum Based on 2 Rows & 1 Column with SUM, INDEX and MATCH Functions Together**

In this section under 2 rows & 1 column criteria, we’ll find out the total selling price of HP & Lenovo devices in June.

**📌**** Steps:**

➤ In **Cell F21**, the formula will be under the given criteria:

`=SUM(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH(F20,D4:I4,0)))`

➤ 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 braces.

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

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

`=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** & you’ll see the output as $25,980.00.

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

**Criteria 6: Finding out Result Based on 2 Rows & All Columns with SUM, INDEX and MATCH Functions Together**

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

`=SUM(INDEX(D5:I14,MATCH(F18,B5:B14,0),0))+SUM(INDEX(D5:I14,MATCH(F19,B5:B14,0),0))`

➤ Press **Enter** & we’ll find the resultant value as $89,870.

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

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

`=SUM(INDEX(D5:I14,0,MATCH(F19,D4:I4,0)))`

➤ Press **Enter** & you’re done. The return value will be $141,230.00.

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

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:

`=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 Result Based on All Rows & All Columns with SUM, INDEX and MATCH Functions Together**

We’ll now find out the total selling price of all devices for all months in the table. Let’s follow the instructions below to use the **SUM,** **INDEX** and **MATCH** functions with multiple criteria to find out the total selling price of all devices for all months!

**📌**** Steps:**

➤ In **Cell F20**, you have to type:

`=SUM(INDEX(D5:I14,0,0))`

➤ Press **Enter** & you’ll get the resultant value as $808,090.00.

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.

**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 together.

**📌**** Steps:**

➤ Under this criterion, our formula in **Cell F22** will be:

`=SUM(INDEX(D5:I14,MATCH({"HP","Lenovo"},B5:B14,0),MATCH({"Apr","Jun"},D4:I4,0)))`

➤ Now press **Enter** & you’ll see the result as $12,730.00.

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.

**Use of SUMIF with INDEX-MATCH Functions to Sum under Multiple Criteria**

Before getting down to the uses of another combined formula, let’s get introduced to **the SUMIF function** now.

**Formula Objective:**

**Add the cells specified by the given 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:**

We’ll use our previous dataset here to keep the flow. With the SUMIF function, we’ll find the total sales in May for desktops only of all brands. So, our 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.

Let’s use **SUMIF** with** INDEX & MATCH** functions to sum under multiple criteria along with columns & rows. Our dataset is now a bit modified. In **Column A**, 5 brands are now present with multiple appearances for their 2 types of devices. Sales prices in the rest of the columns are unchanged.

We’ll 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** & you’ll get the total sales price for Lenovo in June at once.

And if you want to switch to the device category, assuming you want to find the total sales price for the desktop then our **Sum Range** will be **C5:C14** & **Sum Criteria** will be Desktop now. So, in that case, the formula will be:

`=SUMIF(C5:C14,F17,INDEX(D5:I14,0,MATCH(F16,D4:I4,0)))`

**Use of SUMIFS with INDEX & MATCH Functions in Excel**

**SUMIFS** is the subcategory of the **SUMIF** function. Using the **SUMIFS** function and **INDEX & MATCH** functions inside, you can add more than 1 criterion that is not possible with the **SUMIF** function. In **SUMIFS** functions, you have to input the **Sum Range** first, then **Criteria Range**, as well as Range Criteria, will be placed. Now based on our dataset, we’ll find out the sales price of the Acer desktop in May. Along the rows, we’re adding two different criteria here 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** & the function will return as $9,000.00.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**Concluding Words**

I hope all of these methods mentioned above will now prompt you to apply them in your regular Excel chores. If you have any questions or feedback, please let me know through your valuable comments. Or you can have a glance at our other interesting & informative articles on this website.

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

I recreated the table/data you are using as your example but while attempting to use “Use of SUMIFS with INDEX & MATCH Functions in Excel” formula (=SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,F17,C5:C14,F18)), it keeps returning a “#N/A” with the message, “Error Argument must be a range.”

Any idea where I’m going wrong?

The formula is working at our end perfectly. You can download the workbook where we did the whole thing and cross-check, please.

Extracting data based on one Row and Two Columns:Using the downloaded work book, I evaluated the look up value {“Feb”,”Jun”} in the match function and returned 2 as the column position. The returned value was the selling price for only Feb and yet we were looking at 2 columns (Feb and Jun)

Please re-check and advise

Hello Peter and Charlie, the solution described in this article at Criteria 2: “Extracting Data Based on 1 Row & 2 Columns with SUM, INDEX and MATCH Functions Together” is perfectly working from our end.

Just to inform you, you can’t use a cell reference in the Look_up value of the MATCH function instead you have to insert the single of multiple look_up values manually e.g. {“Feb”,”Jun”}. Hope you have found your solution. Try it and let us know the outcome in a reply. Thank you!

I’m running into the same problem. I can’t look up multiple criteria using MATCH in the same row. It only returns one of the values.

Do you have examples where there are two criteria along the top, ex month & year, and in some situations you need full year and in others you need only last month? Thank you

Hello ALLISON!

you can use the “

Use of SUMIF with INDEX-MATCH Functions to Sum under Multiple Criteria” method for your problem. Here, we have used the brand name as criteria and you can substitute it by Year. See this screenshot below:While using this method, you have to fill in both criteria. So, you must put both the year’s and month’s values.

I hope, your problem will be solved in this way. You can share more problems in an email at

[email protected]What if I wanted to sum the value of all Lenovos and all desktops in the month of June without double-counting the Lenovo desktop sold in that month? The total should be 70,700, but if I add the total of all Lenovos for the month of June and all desktops for the month of June, it ends up as 77,280 (this is taken from the table in the second, third, and fourth screenshots of the section entitled ‘Use of SUMIF with INDEX-MATCH Functions to Sum under Multiple Criteria’), because the Lenovo desktop in June (6,580) gets counted twice. How can we avoid this double-counting and use a formula that would arrive at the total of 70,700 (and could it be used if there was a third criterion–OR not AND–that we wanted to sum without double and triple counting the instances where more than one of these criteria was met)?

Hello,

JOHN!Thanks for your comment!

I’m not sure about your problem, you want all Lenovos that means the

DesktopandNotebook! Also, All the desktops that mean the range ofC5:C9!Can you please send me your excel file via email? ([email protected]).

You can use this https://www.exceldemy.com/sum-index-match-multiple-criteria-in-excel/#Use_of_SUMIFS_with_INDEX_MATCH_Functions_in_Excel

This may fulfill your criteria.

Good Luck!

Regards,

Sabrina AyonAuthor,

ExcelDemy.What if I wanted to do QTD numbers by brand?

How do I get QTD by brand?

Dear ALEX,

Thank you for this interesting question. If you want to find the total sales value for each brand within a quarter or QTD, you can use a formula that combines the

SUMIFS,INDEX, andMATCHfunctions.Formula:`=SUMIFS(INDEX(D5:I14,0,MATCH(D16,D4:I4,0)),B5:B14,D17,C5:C14,D18)+SUMIFS(INDEX(D5:I14,0,MATCH(E16,D4:I4,0)),B5:B14,D17,C5:C14,D18)+SUMIFS(INDEX(D5:I14,0,MATCH(F16,D4:I4,0)),B5:B14,D17,C5:C14,D18)`

The formula used here is the same as the one explained in the “

Use of SUMIFS with INDEX & MATCH Functions in Excel” section. We repeat the formula two more times, adjusting the column reference for each specific month in the quarter.I hope this solution resolves your issue. Feel free to email us at

if you have any further problems or inquiries.[email protected]Regards,

Qayem Ishrak Khan

Team ExcelDemy.

Hello,

Can you help me with below

=IFNA(INDEX($G$20:$G$52,MATCH(1,($B$20:$B$53=$B3)*($E$20:$E$52=$D3)*($F$20:$F$52=CH$1)*($A$20:$A$53=$A3)*($D$20:$D$52=$E3),0)),”0″)

this formula returns value based on multiple matches. But when i have more than one value, return is only 1st value. How can i change it to get all values sum instead.

Hello, RUSSEL.

Thank you for sharing your problem with us. We have provided

2different ways to solve your problem.For example, we have a dataset of

Product,Size, andPrice. We will find the total price of theSmall Shirts.Method-1:For this, write the following formula in the

F9cell and pressEnter.`=IFERROR(INDEX($D$5:$D$15, SMALL(IF(($G$4=$B$5:$B$15)*($G$5=$C$5:$C$15), ROW($D$5:$D$15)-ROW($D$5)+1), ROWS($1:1))), "")`

And, you will get the first

Pricevalue.Then Drag the

F9formula downwards to get other Price values that meet the criteria.You can see that we have got two Price values that meet the multiple criteria.

Now, you can use the

SUMfunction to get the total Price value.Method 2:Write the following formula in

F9and pressEnter.`=SUM(IFERROR(FILTER(D5:D15, (G4=B5:B15)*(G5=C5:C15)), ""))`

And, you will get the total Price value with multiple criteria.

The

FILTERfunction used in this method is only available forMicrosoft 365andExcel 2021.Solution to Your Problem:You can use the formula below to solve your problem:

`=SUM(IFERROR(FILTER($G$20:$G$52, ($B$20:$B$53=$B3)*($E$20:$E$52=$D3)*($F$20:$F$52=CH$1)*($A$20:$A$53=$A3)*($D$20:$D$52=$E3)), ""))`

We could have given you the exact formula if we had your dataset. Let us know if your problem is solved.

Regards,

Sourav

ExcelDemy.