How to Sum Multiple Rows Using INDEX MATCH Formula

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.

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

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

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 lookup_array where we may find the search key. Then, we used 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.

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

To do this in our worksheet,

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

To get the sum, we need to use 9 as the function_number. Here, we have used 0 as our behavior_option, feel free to use what matches your desire. The work of the 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.

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

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:

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

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

Download Practice Workbook

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.

<< Go Back to INDEX MATCH | Formula List | Learn Excel