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.

index match sum multiple rows

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.

Using SUM Function

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.

Using SUMPRODUCT Function to Sum Multiple Rows Using INDEX MATCH

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.

Utilizing AGGREGATE Function

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.

Utilizing AGGREGATE Function to Sum Multiple Rows Using INDEX MATCH

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.

Applying SUBTOTAL Function

  • Let’s check it for Havart.

Applying SUBTOTAL Function to Sum Multiple Rows Using INDEX MATCH

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.

Sales of Notebook

Sales of Monitor

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

How to Sum Across Multiple Sheets with INDEX-MATCH Formula

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.

Defining New Names

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

Working on New Name Dialog Box

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

How to Sum Across Multiple Sheets with INDEX-MATCH Formula

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

Using Fill Handle Tool

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

INDEX MATCH Across Multiple Sheets

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.

How to Apply SUMIFS with INDEX MATCH for Multiple Columns

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.

SUMIFS with INDEX MATCH for multiple columns

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.

Practice Section

Download Practice Workbook

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


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

Get FREE Advanced Excel Exercises with Solutions!
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

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


    • Reply Avatar photo
      Md. Abdur Rahim Rasel Aug 21, 2023 at 1:39 PM

      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 following FILTER function to filter Jimmy’s data.
      =FILTER(B5:H16, B5:B16=C18, "no results")
      Filtering Data
      Hence, type the SUM function in cell I21 to calculate cumulative sales.
      Calculating Cumulative Sales of a Particular Sales Representative
      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.xlsx
      If 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.
      Md. Abdur Rahim Rasel
      Exceldemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF