Sum Using OFFSET and MATCH in Excel (With Alternative Options)

SUM using OFFSET from MUltiple Rows and Multiple Columns

Today I will be showing how you can determine any sum using OFFSET and MATCH in Excel from any set of data.

Before going to the main discussion, note that the OFFSET function is available from Excel 2003.


Download Practice Workbook


An Introduction the OFFSET Function of Excel

Let us begin with the data set below.

We have the sales record of a company named Mars Group.

We have the names of the products in column B, and the number of each product sold in the years 2016 to 2020 in columns C, D, E, F and G respectively.

A Data Set in Excel

Now try to accomplish a task.

Make a list of the number of sales of the 5th to 7th products in the 2nd to 4th years.

If you do not have any idea about the OFFSET function of Excel, what you will probably do is that you will drop down along the product name column B counting 1… 2… 3…., and then stop when you reach 5 (Telephone, in this case, cell B8).

Moving Downward in Excel

Then you will move 2 steps right and reach column 2017 (Column D).

That is cell D8.

Moving Rightward in Excel

Then you will collect the data of 3 rows downward (5th to 7th) and 3 columns rightward (2nd to 4th) from that cell.

For your convenience, I have highlighted the data in the image below.

A Range of Cells Highlighted in Excel

For a small set of data, you can do this. But imagine you have a data set of 5000 rows long and 50 columns wide. Then?

Is it possible to execute this manually?

The answer is no. A big no.

Microsoft Excel provides us a function called OFFSET which does exactly the same thing for you.

It takes a cell reference to begin, then moves a specific number of rows down, then again moves a specific number of columns right.

After reaching the destination cell, it collects data of a given number of heights and a given number of widths from that cell.

Just the same thing we did earlier manually.

So the syntax of the OFFSET function is:

 =OFFSET(reference,rows,cols,[height],[width])
  • Reference is the cell reference from where it starts moving. In the previous example, it was B3.
  • Rows is the number of rows it moves downward. In our previous example, it was 5.
  • Cols is the number of columns it moves rightward. In our previous example, it was 2.
  • [height] is the number of rows from the set of data that we are going to collect. In our previous example, it was 3 (row 5th to 7th). This is optional. Default is 1.
  • [width] is the number of columns from the set of data that we are going to collect. In our previous example, it was 3 (column 2nd to 4th). This is also optional. Default is 1.

So, to make a list of the number of sales of the 5th to 7th products in the 2nd to 4th years, our formula would have been:

=OFFSET(B3,5,2,3,3)

OFFSET Function in Excel

See, we have extracted the same data using the OFFSET function.

Hope you have understood how the OFFSET function works.

Note: OFFSET is an Array Function. So each time you use the function, you have to press Ctrl + Shift + Enter unless you are in Office 365.


Sum Using OFFSET and MATCH Functions in Excel

This time we shall try to determine some sums using the OFFSET function of Excel, along with the SUM and MATCH functions.

If you do not have an idea about the SUM and the MATCH functions of Excel, visit this link (For SUM) and this link (For MATCH) to attain the required knowledge.


1. Sum in Single Row and Multiple Columns Using OFFSET and MATCH Functions of Excel

Let us try to find out the total number of Table Fans sold from 2016 to 2020.

What will you do?

I am telling you. First, you will tell the OFFSET function to start moving from cell B3.

Then you will tell it to move downward up to the cell where there is a Table Fan.

But the problem is that OFFSET can’t read like you. It can’t find out where there is the Table Fan in the product name column.

You have to tell it specifically the number of rows it needs to move downward to find the Table Fan.

How to do that?

The MATCH function of Excel will come in handy here.

You can use this formula to find out the number of rows it needs to move downward to meet the Table Fan.

=MATCH("Table Fan",B4:B16,0)

MATCH Function in Excel

Notice: In the Lookup_Array of the MATCH formula we started from the B4, not B3.

Because the MATCH function starts counting right from the first cell.

But the OFFSET function starts counting from one cell below the reference cell.

Now, after moving down and meeting the Table Fan, you need to tell the OFFSET to go to the first year.

Because we need data from all the years here.

So tell it to move to 1 column right.

Then we need just the data of a single row, only of the Table Fan row.

So the height of the data that we shall fetch will be 1.

And finally, the width of the data. We need data from all the years.

So we need to count the total number of years up to 2020.

You can use another MATCH function of Excel to count the cells from C3 to G3.

=MATCH(2020,C3:G3,0)

A MATCH Formula in Excel

So the total formula to sort out the number of sales of Table Fan in all the years will be:

=OFFSET(B3,MATCH("Table Fan",B4:B16,0),1,1,MATCH(2020,C3:G3,0))

Single Row Data by Using OFFSET-MATCH

See we have the sales of Table Fan in each of the years.

Finally, wrap the formula within a SUM function to get the total number of sales of Table Fan.

=SUM(OFFSET(B3,MATCH("Table Fan",B4:B16,0),1,1,MATCH(2020,C3:G3,0)))

Single Row Sum Using OFFSET-MATCH

See, we have got the total sales of Table Fan, it is 6376.

Now, can you tell me the formula to determine the total sales of DVDs?

It is easy. The formula will be:

=SUM(OFFSET(B3,MATCH("DVD",B4:B16,0),1,1,MATCH(2020,C3:G3,0)))

Now if I again ask you, can you tell me the formula to determine the total sales of DVDs, but this time up to the year 2018?

You will say this is also easy. Just change the lookup_value in the second MATCH function to 2018 from 2020.

=SUM(OFFSET(B3,MATCH("DVD",B4:B16,0),1,1,MATCH(2018,C3:G3,0)))

Then, if I again ask you, what is the formula to determine the total sales of DVDs, but this time from 2017 to 2018, not from 2016?

Don’t worry. This is also easy. Remember the syntax of the OFFSET function?

=OFFSET(reference,rows,cols,[height],[width])

We used two MATCH formulas in place of the arguments rows and [width].

We set the argument cols to 1 because we were collecting data starting from the first column.

If you do not want to start from the first column, like in this case, starting from 2017, not 2016, use another MATCH function in place of the cols argument.

In this case, the function will be:

=MATCH(2017,C3:G3,0)

And in place of the [width] argument, insert:

=(MATCH function of the ending year - MATCH function of the starting year)+1

In this case:

=MATCH(2018,C3:G3,0)-MATCH(2017,C3:G3,0)+1

So the complete formula will be:

=SUM(OFFSET(B3,MATCH("DVD",B4:B16,0),MATCH(2017,C3:G3,0),1,MATCH(2018,C3:G3,0)-MATCH(2017,C3:G3,0)+1))

Single Row Sum Using OFFSET-MATCH

See, we have found out the total sales of DVDs from 2017 to 2018, 174.

Finally, if you understand everything, let’s make a calculator on the worksheet.

It will take 3 inputs:

  • The product name is in cell J4.
  • The starting year is in cell J5.
  • And the ending year is in cell J6.

And will return the total number of sales in cell J6.

The formula will be:

=SUM(OFFSET(B3,MATCH(J4,B4:B16,0),MATCH(J5,C3:G3,0),1,MATCH(J6,C3:G3,0)-MATCH(J5,C3:G3,0)+1))

Single Row Sum Calculator

Enter the inputs according to your wish, and you will get the desired output.

I entered Laptop, 2017 and 2019. And got the total number of sales as 1510.


2. Sum in Multiple Rows and Single Column Using OFFSET and MATCH Functions of Excel

Now, can you tell me what the formula will be to determine the total number of sales in the year 2018?

If you understand the previous section, this should be easy for you.

Remember the syntax of the OFFSET function?

=OFFSET(reference,rows,cols,[height],[width])

The reference argument will be cell B3 because we shall again start from B3.

If you want the sales of all the products, the rows argument will be 1.

Because we want to start collecting data from just below the reference cell.

For the cols argument, you have to use a MATCH function.

The MATCH function will tell OFFSET how many columns to move rightward.

As we want the sales from 2018, the MATCH formula will be:

=MATCH(2018,C3:G3,0)

For the [height] argument, you have to use another MATCH function.

If you want the total sales of all the products, the MATCH function to determine the height of your expected data will be:

=MATCH("Motorcycle",B4:B16,0)

We have used “Motorcycle” because it is the last product.

And as you want data from just 1 year, the [width] argument will be 1.

So the total formula to extract out all the sales of 2018 will be:

=OFFSET(B3,1,MATCH(2018,C3:G3,0),MATCH("Motorcycle",B4:B16,0),1)

Single Column Data by Using OFFSET-MATCH

See, we have filtered out all the sales from 2018.

And to find out the total number of sales, wrap the whole formula within a SUM function:

=SUM(OFFSET(B3,1,MATCH(2018,C3:G3,0),MATCH("Motorcycle",B4:B16,0),1))

Single Column SUM Using OFFSET-MATCH

Now if you want the sales of not all the products, only from Smartphone to Radio, then?

Pretty easy. In place of the rows argument of the OFFSET function, insert this MATCH function:

=MATCH("Smartphone",B4:B16,0)

And in place of the [height] argument, enter:

=(MATCH formula of the last product - MATCH formula of the first product) +1

In this case, will be:

=(MATCH("Radio",B4:B16,0)-MATCH("Smartphone",B4:B16,0))+1

So the complete formula to determine the total sales of 2018 from Smartphone to Radio will be:

=SUM(OFFSET(B3,MATCH("Smartphone",B4:B16,0),MATCH(2018,C3:G3,0),(MATCH("Radio",B4:B16,0)-MATCH("Smartphone",B4:B16,0))+1,1))

Single Column Sum by Using OFFSET-MATCH

Now the calculator.

Enter the year in cell J4.

The first product is in cell J5.

And last product is in cell J6.

The calculator formula will be:

=SUM(OFFSET(B3,MATCH(J5,B4:B16,0),MATCH(J4,C3:G3,0),(MATCH(J6,B4:B16,0)-MATCH(J5,B4:B16,0))+1,1))

Single Column SUM Calculator


3. Sum in Multiple Rows and Multiple Columns Using OFFSET and MATCH Functions of Excel


Case 1: Adjacent Rows and Columns

Now another question.

What will be the formula to determine all the sales from 2017 to 2019, and from Telephone to DVD?

We bring the syntax of the OFFSET function back:

=OFFSET(reference,rows,cols,[height],[width])

The reference argument will be cell B3. No change.

The rows argument will be a MATCH function. The MATCH function to tell OFFSET how many rows it needs to move downwards to find the Telephone.

It will be:

=MATCH("Telephone",B4:B16,0)

The cols argument will be another MATCH function. The MATCH function to tell OFFSET how many rows it needs to move rightward to find 2017

In this case, it will be:

=MATCH(2017,C3:G3,0)

The [height] argument will be:

=(MATCH formula of the last product - MATCH formula of the first product) +1

In this case:

=MATCH("DVD",B4:B16,0)-MATCH("Telephone",B4:B16,0)+1

And the [width] argument will be:

=(MATCH formula of the ending year - MATCH formula of the starting year) +1

In this case:

=MATCH(2019,C3:G3,0)-MATCH(2017,C3:G3,0)+1

So the formula to determine all the sales from 2017 to 2019, and from Telephone to DVD will be:

=OFFSET(B3,MATCH("Telephone",B4:B16,0),MATCH(2017,C3:G3,0),MATCH("DVD",B4:B16,0)-MATCH("Telephone",B4:B16,0)+1,MATCH(2019,C3:G3,0)-MATCH(2017,C3:G3,0)+1)

Multiple Row and Columns Data Using OFFSET-MATCH

To find out the total number of sales, wrap the complete formula within a SUM function:

=SUM(OFFSET(B3,MATCH("Telephone",B4:B16,0),MATCH(2017,C3:G3,0),MATCH("DVD",B4:B16,0)-MATCH("Telephone",B4:B16,0)+1,MATCH(2019,C3:G3,0)-MATCH(2017,C3:G3,0)+1))

SUM using OFFSET from MUltiple Rows and Multiple Columns

See the total number of sales from 2017 to 2019, and from Telephone to DVD, was 12934 items.

Now let’s produce the calculator.

We will enter the starting year in cell J4.

Ending year in cell J5.

The first product is in cell J6.

Last product in cell J7.

And get the total number of sales in cell J8.

The formula for the calculator will be:

=SUM(OFFSET(B3,MATCH(J6,B4:B16,0),MATCH(J4,C3:G3,0),MATCH(J7,B4:B16,0)-MATCH(J6,B4:B16,0)+1,MATCH(J5,C3:G3,0)-MATCH(J4,C3:G3,0)+1))

Multiple Rows and Columns Sum Calculator


Case 2: Non-Adjacent Rows and Columns

In the previous section, we learned how we can filter out some data from multiple rows and columns from a data set.

But in that case, all the rows and columns were adjacent.

Now if I ask you, filter out the number of sales of Laptop, Telephone, Television, and Private Car in the years 2017, 2019, and 2020, can you accomplish it?

Something to worry about. We need data from four rows and three columns, but none of them are adjacent.

Do not worry. First of all, make an empty table with the names of the rows and columns.

Empty Table for Non Adjacent Multiple Rows and Columns Using OFFSET-MATCH

Then go to the first cell of the empty table (In my case, J4).

Enter a formula that extracts out the number of sales of the first product you want (In this case Laptop) from the first year you want (In this case 2017).

How to do that?

The reference of the OFFSET function is, as usual, cell B3.

The rows argument will be a MATCH function, the number of steps the OFFSET has to move downward to find the first product Laptop (I4) in column B.

=MATCH( $I4,$B$4:$B$16,0)

The cols argument will be another MATCH function, the number of steps the OFFSET has to move rightward to find the first year, 2016 (J3) from cell C3 to G3.

=MATCH(J$3,$C$3:$G$3,0)

The [height] and the [width] arguments will be 1, as we want to extract data from a single cell this time.

So the complete formula will be:

=OFFSET($B$3, MATCH( $I7,$B$4:$B$16,0), MATCH(J$3,$C$3:$G$3,0),1,1)

OFFSET-MATCH for Non-Adjacent Multiple Rows and Columns

See we have the first cell of the table filled with the sales number of the first product laptop in the first year 2017.

Then drag the Fill Handle along both the rows and columns of the table.

You will find all the remaining cells filled with the expected number of sales.

Dragging Fill Handle in OFFSET-MATCH Formula

Finally, use a SUM function to calculate the total sales of this table.

=SUM(J4:L7)

SUM of Multiple Rows and Columns

See the total number of sales is 7365.


Case 3: Alternative Options

If you want, you can use this FILTER formula to extract out all the sales from the specific rows and columns:

=FILTER(INDEX(C4:G16,MATCH(B4:B16,B4:B16,0),MATCH(J3:L3,C3:G3,0)),ISNUMBER(MATCH(B4:B16,I4:I7,0)))

FILTER Formula to SUM for OFFSET-MATCH

Then wrap it within a SUM function to calculate the total sales:

=SUM(FILTER(INDEX(C4:G16,MATCH(B4:B16,B4:B16,0),MATCH(J3:L3,C3:G3,0)),ISNUMBER(MATCH(B4:B16,I4:I7,0))))

FILTER Formula for OFFSET-MATCH

See, we got the same number of sales as above, 7365.

Or if you can’t use the FILTER function, use this IF formula to calculate the total sales:

=SUM(IF(ISNUMBER(MATCH(B4:B16,I4:I7,0)),INDEX(C4:G16,MATCH(B4:B16,B4:B16,0),MATCH(J3:L3,C3:G3,0)),0))

SUM of Multiple Rows and Columns


4. OFFSET MATCH with Criteria in Excel

Finally, if you have to find out the total number of sales maintaining any specific criteria, use the OFFSET formula inside a SUMIF function.

For example, to determine the total number of sales from 2017 to 2019, and from Laptop to Radio, but of the ones greater than 500 only, will be:

=SUMIF(OFFSET(B3,MATCH("Laptop",B4:B16,0),MATCH(2017,C3:G3,0),MATCH("Radio",B4:B16,0)-MATCH("Laptop",B4:B16,0)+1,MATCH(2019,C3:G3,0)-MATCH(2017,C3:G3,0)+1),">500")

OFFSET-MATCH with Criteria

See the total number of sales of the ones greater than 500 is 15688.


Conclusion

Using these methods, you can calculate any sum using the OFFSET and MATCH functions of Excel. Do you know any other method? Let us know in the comment section.


Further Readings:

How to Sort Data in Excel Using Formula

Auto Sort Multiple Columns in Excel (3 Ways)

How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)

How to Sort Multiple Columns in Excel (5 Quick Approaches)

Sorting Columns in Excel While Keeping Rows Together

How to Arrange Numbers in Ascending Order in Excel using Formula

How To Sort Alphabetically In Excel And Keep Rows Together

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. Good stuff!

    I have underestimated Offset function for a long time

Leave a reply

ExcelDemy
Logo