You may need to calculate the sum of multiple rows that match any criteria. Today we will show you how to apply **INDEX – MATCH** to sum multiple rows. For conducting the session, we are going to use Excel 2019. You can use your preferred version.

**Read more:** **Sum with INDEX-MATCH Functions under Multiple Criteria in Excel**

First things first, let’s get to know about today’s workbook which is going to be the base of our examples.

We have a table that contains the monthly purchase amount by several customers. Using this table we will find the sum of multiple rows with the help of **INDEX – MATCH. **

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

## Practice Workbook

You are welcome to download the practice workbook from the link below

## Basics of INDEX-MATCH

### 1. INDEX Function

The** INDEX **function returns a value or the reference to a value from within a table or range. It can be used to retrieve individual values, or any entire rows and columns. Let’s see the syntax of the **INDEX** function.

`INDEX(array/reference, row_number, column_number,area_number)`

**array or reference:** A cell or range of cells to look at

**row_number:** A row in an array from which to return a value

**column_number:** The column in an array from which to return a value

**area_number:** Selects a range in reference from which to return the intersection of *row_num* and *column_num*. This is an optional field.

While writing the formula you can choose whether to provide *row_number* or *column_number*. If you provide *row_number* then it’s optional to use *column_number* and vice versa.

To know more about the **INDEX **function, visit the Microsoft Support site.

### 2. MATCH Function

One function that you will find more often with the **INDEX** function is the **MATCH** function.

The **MATCH** function is used to locate the position of a specified item in a range of cells. It returns the relative position of a particular item in the range.

Syntax of the **MATCH** function is

`MATCH(lookup_value, lookup_array, match_type)`

**lookup_value: **The value to search in the lookup_array.

**lookup_array:** A range of cells that are being searched.

**match_type:** This is an optional field. You can insert 3 values.

- 1 = Smaller or equal to lookup_value
- 0 = Exact lookup_value
- -1 = Greater or equal to lookup_value

To know more about the **MATCH **function, visit the Microsoft Support site.

## 3 Ways of INDEX MATCH Sum Multiple Rows

As you already know, our agenda for this article is to calculate the sum of multiple rows with the help of the **INDEX **and **MATCH **functions.

To show you the ways with simple examples, let’s imagine a scenario where you want to find the total purchase of any customer across several months.

### 1. SUM family 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.

`SUM(number1,[number2],..)`

**number1: **The first value to sum

**number2: **The second value to sum.

You can insert as many numbers as you want, apart from the *number1* all are optional.

Your provided numbers can be within a range, in practical use, more often you need to provide a range of numbers inside the function.

Here we will provide the numbers as a range, and the **INDEX – MATCH **will do the trick for us.

Our formula will be something like this

`SUM(INDEX(number_array,,MATCH(lookup_value,lookup_array,0)))`

Write the formula in Excel.

Here we have inserted the range of numbers inside the **INDEX **function as the array.

You can see, then 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 *lookup_array* where we may find the search key.

We have used 0, which denotes the *Exact Match. *

It gave us the total amount of purchases made by customer *Jay. *

Instead of the **SUM **function, you can use the **SUMPRODUCT **function.

The formula will be the same as the previous, the only change is using the **SUMPRODUCT** function in place of the **SUM** function.

`SUMPRODUCT(INDEX(number_array,,MATCH(lookup_value,lookup_array,0)))`

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

### 2. AGGREGATE Function

To find the sum or total we can use the **AGGREGATE **function as well.

The **AGGREGATE** function returns an aggregate calculation like *AVERAGE*, *COUNT*, *MAX*, *SUM, etc*.

The syntax for the **AGGREGATE **function is as follows

`AGGREGATE(function_number,behavior_option, range)`

**function_number: **This number specifies which calculation should be made.

**behavior_option: **Set this using number. This number denotes how the function will behave.

**range: **Range you want to aggregate.

There is a total of 19 operations, specified by the predefined *function_number *available in the **AGGREGATE **function. We are listing few frequently used function numbers

Function | Function_number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

LARGE | 14 |

SMALL | 15 |

There are 8 possible values for behavior_options. They are

Value | Behavior |
---|---|

0 | Ignore SUBTOTAL and AGGREGATE functions |

1 | Ignore hidden rows, SUBTOTAL and AGGREGATE functions |

2 | Ignore error values, SUBTOTAL and AGGREGATE functions |

3 | Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions |

4 | Ignore nothing |

5 | Ignore hidden rows |

6 | Ignore error values |

7 | Ignore hidden rows and error values |

We are leaving the link to the Microsoft Support site for the **AGGREGATE **function, which will help you know the function much deeper.

You can see to get the sum we need to use 9 as *function_number. *For the behavior_option you can choose any of the numbers your circumstances demand.

Our formula will be

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

Write the formula in Excel.

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.

For the customer *Jay, *we have found the total amount of purchases.

Let’s change the criteria (customer name)

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

### 3. SUBTOTAL Function

You can do the task using the **SUBTOTAL **function as well.

The **SUBTOTAL** function returns an aggregate result for supplied values. Similar to the **AGGREGATE **function, **SUBTOTAL** also may return the SUM, AVERAGE, COUNT, MAX, and some others.

The syntax for the **SUBTOTAL **function is as follows

`SUBTOTAL (function_num, array_ref1, [array_ref2], ...)`

**function_num: – **A number that specifies which function to use in calculating subtotals within an array or list.

**array_ref1: **Range or the reference you want to subtotal.

**array_ref2: **Range or the reference you want to subtotal. This is an optional field.

We are listing the function numbers used in the **SUBTOTAL **function.

Function Number | Include Hidden | Ignore Hidden |
---|---|---|

AVERAGE | 1 | 101 |

COUNT | 2 | 102 |

COUNTA | 3 | 103 |

MAX | 4 | 104 |

MIN | 5 | 105 |

PRODUCT | 6 | 106 |

STDEV | 7 | 107 |

STDEVP | 8 | 108 |

SUM | 9 | 109 |

VAR | 10 | 110 |

VARP | 11 | 111 |

We are leaving the link to the Microsoft Support site for the **SUBTOTAL **function, which will help you know the function much deeper.

So depending on the situation, we can use 9 or 109 for summing up the value.

Generic formula using the **SUBTOTAL** function will be

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

Write the function in Excel.

Here we have chosen 9 since we wanted to include the hidden values (though we didn’t have any hidden).

The work of the **INDEX-MATCH **function is the same as the previous, it returns the range where we need to execute our operation.

It returned the *Total Purchase *made by the customer *Max. *

Let’s change the customer and see what output comes

Changing the customer, we have found his *Total Purchase. *

## Conclusion

That’s all for today. We have tried listing several ways to find the sum of multiple rows using the **INDEX – MATCH** function. Hope you will find this helpful. Feel free to comment if something seems difficult to understand. Let us know any other methods that we might have missed here.

## Further Readings

**Index Match Multiple Criteria in Rows and Columns in Excel****SUMPRODUCT with INDEX and MATCH Functions in Excel****SUMIF with INDEX and MATCH Functions in Excel****INDEX, MATCH and MAX with Multiple Criteria in Excel****INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)****How to use INDEX & MATCH worksheet functions in Excel VBA**