Sum with INDEX-MATCH Functions under Multiple Criteria in Excel

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.

Read more: INDEX MATCH Multiple Criteria in Excel

sum with index match multiple criteria overview

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.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare 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.

sum introduction

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.

sum introduction

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.

index introduction


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.

match introduction

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.

match introduction


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

Read more: Index Match Multiple Criteria in Rows and Columns in Excel

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.

index-match-combination


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.

sum index match 1 row 1 column criteria


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.

sum index match 1 row 2 columns criteria

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.

sum index match 1 row all columns criteria

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.

sum index match 2 rows 1 column criteria

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.

sum index match 2 rows 2 columns criteria

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.

sum index match 2 rows 1 column criteria


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.

sum index match all rows 1 column criteria


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.

sum index match all rows 2 columns criteria


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.

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

sum index match all rows all columns criteria

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.

sum index match distinct pairs criteria

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.

sumif introduction

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.

sumif with index match to sum under multiple criteria

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.

sumif with index match to sum under multiple criteria

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

sumif with index match to sum under multiple criteria


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.

sumifs with index match to sum under multiple criteria


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.


You May Also Like to Explore

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

2 Comments
  1. 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?

Leave a reply

ExcelDemy
Logo