Index Match Sum Multiple Rows

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.

Excel sheet - Index Match Sum Multiple Rows

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. 1 = Smaller or equal to lookup_value
  2. 0 = Exact lookup_value
  3. -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.

Scenario sheet - Index Match Sum Multiple Rows

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.

SUM function - Index Match Sum Multiple Rows

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. 

SUM result - Index Match Sum Multiple Rows

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)))

SUMPRODUCT - Index Match Sum Multiple Rows

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.

AGGREGATE - Index Match Sum Multiple Rows

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.

AGGREGATE result-Index Match Sum Multiple Rows

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

Let’s change the criteria (customer name)

AGGREGATE example - Index Match Sum Multiple Rows

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.

SUBTOTAL - Index Match Sum Multiple Rows

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.

SUBTOTAL result-Index Match Sum Multiple Rows

It returned the Total Purchase made by the customer Max. 

Let’s change the customer and see what output comes

Example SUBTOTAL - Index Match Sum Multiple Rows

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo