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.

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

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

That is cell **D8**.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

**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.

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

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.

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

`=SUM(J4:L7)`

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

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

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

### 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")`

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

Good stuff!

I have underestimated Offset function for a long time

Thanks, Crispo, for your feedback.