Microsoft Excel is a powerful program where we can perform numerous operations on our datasets using its tools and features. In many instances, you may need to calculate the sum of multiple rows that match any criteria. In this article, we’ll demonstrate how to use the **INDEX** and **MATCH** functions to sum multiple rows using numerous methods in Excel. So, let’s explore them one by one.

**Table of Contents**hide

## How to Sum Multiple Rows Using INDEX MATCH Formula: 3 Methods

First things first, let’s get to know about today’s dataset, which is going to be the basis of our examples. We have a **Customer-wise Sales** report of a particular company in our hand. This dataset includes **Month Name**, different **Customer’s Name** and their purchasing amounts in columns **B**, **C**, **D**, **E**, **F**, and **G** respectively.

Using this table, we will find the sum of multiple rows with the help of **INDEX-MATCH**. To show you the way with simple examples, let’s imagine a scenario where you want to find the **Total Purchase** of any **Customer** across several months.

To keep things simple for you, this is a basic table with dummy data. In the practical scenario, you may encounter a much larger or more complex dataset.

Here, we have used the *Microsoft Excel 365* version. You may use any other version according to your convenience.

### 1. Using SUM Function

Since we want to find the sum, **the SUM function** is going to come into the task.

The **SUM** function returns the total of a given range of numbers supplied to the function. Your provided numbers can be within a range. In practical use, more often you need to provide a range of numbers inside the function. So, let’s follow the steps below.

**📌**** Steps:**

Our generic formula will be something like this

**=SUM(INDEX(number_array,,MATCH(lookup_value,lookup_array,0)))**

- At the very beginning, go to cell
**D14**and enter the following formula.

`=SUM(INDEX(C6:G11,,MATCH(D13,C5:G5,0)))`

Here we have inserted the range of numbers inside **the INDEX function** as the array. You can see, we inserted two **commas (,)** there. If you remember the syntax of the **INDEX** function, then you might have understood the array, row, and column numbers inside the **INDEX** function. Here we leave the placeholder for the *row *number. We did so that all the rows came into the count. **The MATCH function** returns the column number here. Within the MATCH function, we have inserted the search key as our ** lookup_value** and a

**where we may find the search key. Then, we used**

*lookup_array***0**, which denotes the

**.**

*Exact Match*- After that, press the
**ENTER**key.

It returned to us the total amount spent by customer **Jay**.

Instead of the **SUM** function, you can use **the SUMPRODUCT function**. The formula will be the same as the previous one. The only change will be using the **SUMPRODUCT** function in place of the **SUM** function.

- First of all, select cell
**D14**and insert the following formula.

`=SUMPRODUCT(INDEX(C6:G11,,MATCH(D13,C5:G5,0)))`

- After that, press
**ENTER**.

The **SUMPRODUCT** function works better for an array.

### 2. Utilizing AGGREGATE Function

In this method, we’ll take the help of **the AGGREGATE function**. So let’s begin.

**📌**** Steps:**

Our generic formula will be like the following.

**=AGGREGATE(9,behavior_option,INDEX(number_array,,MATCH(lookup_value,lookup_array,0)))**

To do this in our worksheet,

- At first, select cell
**D14**and write down the following formula.

`=AGGREGATE(9,0,INDEX(C6:G11,,MATCH(D13,C5:G5,0)))`

To get the sum, we need to use **9** as the ** function_number**. Here, we have used

**0**as our

**, feel free to use what matches your desire. The work of the**

*behavior_option***INDEX-MATCH**function is the same as the

**previous**, it returns the range where we need to execute our operation.

- After that, hit the
**ENTER**key.

Here, for the customer **Jay**, we have found the **Total Purchase** amount.

Let’s change the criteria (**Customer** name) and see if our formula works or not.

The formula gave us the **Total Purchase** amount made by **Wernar** from **January** to **June**.

### 3. Applying SUBTOTAL Function

You can do the same task using **the SUBTOTAL function** as well. The **SUBTOTAL** function returns an aggregate result for supplied values. Similar to the **AGGREGATE** function, **SUBTOTAL** also returns the **SUM**, **AVERAGE**, **COUNT**, **MAX**, and some others. It’s simple and easy; just follow along.

**📌**** Steps:**

The generic formula using the **SUBTOTAL** function will be the following.

**=SUBTOTAL(9/109,INDEX(number_array,,MATCH(lookup_value,lookup_array,0)))**

- Firstly, go to cell
**D14**and paste the formula below.

`=SUBTOTAL(9,INDEX(C6:G11,,MATCH(D13,C5:G5,0)))`

Here we have chosen 9 since we wanted to include the hidden values (though we didn’t have any hidden ones). The work of the **INDEX-MATCH** function is the same as the **previous one**.

- Secondly, press
**ENTER**.

- Let’s check it for
**Havart**.

## How to Sum Across Multiple Sheets with INDEX-MATCH Formula

It’s a common scenario to look up and then extract data from multiple sheets based on different criteria. The combination of **INDEX** and **MATCH** functions is a suitable method that can serve the purpose of pulling out data from multiple sheets into a particular one. So, follow these simple steps to sum across multiple sheets using the **INDEX-MATCH** functions.

**📌**** Steps:**

Here, we have the **Sales** amount of 3 different products in 3 different worksheets. See the following images.

In the following sheet, the **Sales** of a particular **Device** or component on a particular **Date** will be extracted from other corresponding worksheets.

Now, see the working steps to solve this problem.

- In the
**Notebook**worksheet, select the entire table**(B4:C10)**first. - Then, go to the
**Formulas**tab and select the**Name Manager**command on the**Defined Names**group.

Immediately, the **Name Manager** dialog box appears before us.

- Here, select the
**New**button.

- In the
**New Name**dialog box, type**Notebook**in the**Name**box. - Additionally, give the cell reference of
**B5:C10**in the**Refers to**box. - Lastly, click
**OK**.

- Similarly, follow the
**previous step**for all other worksheets and name the corresponding tables with the device or components present in the**Summary**sheet. - After that,
**Close**the**Name Manager**dialogue box and you’re now ready to assign the formula in the**Summary**sheet.

- In the first output cell
**D5**, type the following formula:

`=INDEX(INDIRECT(B5&"[Sales]"),MATCH(Summary!$C5,INDIRECT(B5&"[Date]"),0))`

- As usual, press
**ENTER**.

- At this time, bring the cursor to the right-bottom corner of cell
**D5**and instantly it’ll look like a plus**(+)**sign. Actually, it’s the**Fill Handle**tool. - Now, double-click on it.

Finally, you’ll be able to display the sales of other components or devices on the specified dates.

## How to Apply SUMIFS with INDEX MATCH for Multiple Columns

While working in Excel, we often need to do some conditional sums, and to do this we can use **the SUMIFS function** along with the combination of **INDEX** and **MATCH** functions for multiple columns.

We have “Monthly Sales of ABC Company” for various **Products** and for 3 **Sales Persons**.

We aim to find the **Sales **of a particular **Sales Person** based on the **Month** and **Product. **Let’s see the procedure below to use the **SUMIF** with **INDEX** and **MATCH** functions.

**📌**** Steps:**

- Initially, we wrote down the
**Name**of “Sales Person”,**Product**and**Month**in cells**D18**,**D19**, and**D20**respectively. - Then, go to cell
**D21**and put in the following formula into the cell.

`=SUMIFS(INDEX($D$5:$H$16,,MATCH($D$20,$D$4:$H$4,0)),$B$5:$B$16,$D$18,$C$5:$C$16,$D$19)`

- After that, tap the
**ENTER**key.

## Practice Section

For doing practice by yourself, we have provided a *Practice* section like the one below in each sheet on the right side. Please do it by yourself.

You may download the following Excel workbook for better understanding and practice yourself.

## Conclusion

This article explains how to convert text to columns without overwriting in Excel in a simple and concise manner. Don’t forget to download the practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

## Further Readings

- How to Match Multiple Criteria from Different Arrays in Excel
- Use INDEX MATCH for Multiple Criteria Without Array
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
- How to Use IF with INDEX & MATCH Functions in Excel
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- SUMPRODUCT with INDEX and MATCH Functions in Excel

Excellent post, it help me to find a solution for my problem.

In the last part of the post, I would be interested if there is a way to calculate the cumulative sales let’s say for Jimmy from Jan-22 to May-22.

Thanks so much,

Javier

Hello JAVIER!

Thank you for sharing your problem with us. We have got a very simple solution to your problem. Let’s follow the instructions below to fix your problem.

In cell

B21, write down the followingFILTERfunction to filter Jimmy’s data.`=FILTER(B5:H16, B5:B16=C18, "no results")`

Hence, type

the SUM functionin cellI21to calculate cumulative sales.`=SUM($D$21:H21)`

As a result, you will be able to calculate the cumulative sales of a particular sales representative.

Please download the Excel file for solving your problem and practice with it.

Cumulative Sales.xlsxIf you are still facing issues, please mail us at the address below.

[email protected]

Once again, thank you for your appreciation and for being with us.

Regards

Md. Abdur Rahim RaselExceldemy Team