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.

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.

## Sum Multiple Rows with INDEX MATCH

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.