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:
And the resulting formula will be:
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:
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:
Let’s take a look at the portion of the formula that created the C7 reference:
The ADDRESS function has the following arguments in its syntax:
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!