How To SUM A Range Of Values In An Array Using INDIRECT, ADDRESS, and MATCH Functions In Excel

In this tutorial we are going to explore a variation of the traditional INDEX/MATCH combination to extract and sum only a portion of a row or column within an array.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

In a previous tutorial we used the traditional INDEX and MATCH function combination to pull the data from the intersection of a row and column. Another tutorial showed how we can also sum either that row or column using the same functions. Here are the links to those tutorials:

You can find the basic INDEX/MATCH tutorial here.

You can find the INDEX/MATCH tutorial that includes summing the rows or columns here.

But what if you don’t want to sum the entire row or column of data from the array, but just a portion, and you want that range to be dynamic, so you can choose the values you want to SUM. Well, to accomplish that, we are going to use a combination of the following functions:

SUM
INDIRECT
ADDRESS
MATCH

And the resulting formula will be:

=SUM(INDIRECT(ADDRESS(MATCH(B21,$A$1:$A$11,0),MATCH(B22,$A$1:$M$1,0),1,1)):
INDIRECT(ADDRESS(MATCH(B21,$A$1:$A$11,0),MATCH(B23,$A$1:$M$1,0),1,1)))

Although it looks quite ominous, don’t worry. We’ll break it down into small, bite-sized pieces for you to digest!.

There are basically four components to this formula:

1) The SUM function. We are using this basic method to add the range of values.
2) The ADDRESS function which creates a cell reference AS TEXT.
3) The MATCH function, which will give us the row and column numbers for the ADDRESS function.
4) The INDIRECT function, which will convert the TEXT version of the cell reference created by the ADDRESS function to a usable cell reference for the SUM function.

In essence, if we wanted to add the values for Prod6 for February to November and were doing it manually, the SUM function would look like this:

=SUM(C7:L7)

122315-1

So we need a way to dynamically create the C7 and L7 cell references. We added drop down lists to be able to select a starting and ending month:

122315-2

Let’s take a look at the portion of the formula that created the C7 reference:

INDIRECT(ADDRESS(MATCH(B21,$A$1:$A$11,0),MATCH(B22,$A$1:$M$1,0),1,1))

The ADDRESS function has the following arguments in its syntax:

=ADDRESS(row_number,column_number,[abs_num],[a1],[sheet_text])

The row number and column number are taken care of using the typical MATCH functions. Again, you can watch or review the previous tutorials on using the MATCH function.

The abs_num is just a way to determine if you want an absolute, relative or mixed cell reference. In our case we chose #1, which is absolute.

The result of the ADDRESS function is “$C$7”, but as text. And as we noted above, the INDIRECT function will take a text string and convert it to a usable cell reference. The same logic is used to generate the second cell reference in our SUM function. It’s really that simple.

Take a few minutes and watch the video and I’m sure you’ll see how logical the process is to create this formula!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

2 Responses to How To SUM A Range Of Values In An Array Using INDIRECT, ADDRESS, and MATCH Functions In Excel

  1. Julie January 25, 2017 at 4:04 pm #

    Hi Michael,
    Thank you for your posts. I have found numerous of these helpful for what I’m trying to accomplish. I believe this is the formula combination that I need, but my data is on different tabs within my excel workbook.

    I’m building a financial reporting tool. I’d like to easily be able to compare YTD balances for Actual vs Budget vs Forecast 1 vs Forecast 2, etc. The flexibility of Index, Match and Indirect has been great. But now I need to be able to sum for a selected period of time for YTD.

    Using this post, I have my Beg Date and Ending Date. When I run the match criteria – lookup array, I’m trying to reference the cell with the respective tab name, i.e. Actual, Budget.
    I’m trying to avoid a sumif=”Actual”.

    I’m happy to try to share additional details, but I’m very stuck on this.
    thanks

    • Michael Rempel January 26, 2017 at 5:34 pm #

      Julie, sorry I did not see your comment until today. I don’t know why I didn’t get a notification. If you can send me your file or a sample to show me what you need, I’ll be happy to look at it. Send it to mrempel@excel-bytes.com.

Leave a Reply