Microsoft Excel is a powerful program, and we use it on a regular basis. However, we can easily calculate the sum in Excel using **the OFFSET** and **MATCH functions** in addition to **the SUM function**. In this article, I will show you **4 **ideal examples to determine any **sum using OFFSET and MATCH **in **Excel **from any set of data. Hence, read the article carefully and save time.

**Table of Contents**hide

## Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

## 4 Ideal Examples of Sum Using OFFSET and MATCH in Excel

In this section, I will demonstrate **4 **ideal examples for calculating sum using **OFFSET **and **MATCH **functions in **Excel**. For the purpose of demonstration, we have used the following simple dataset. Here, we have the sales record of a company named **Mars Group**. However, we have the names of the **Products **in column **B** and the number of each product sold in the years **2020 **and **2021 **in columns **C **and **D, **respectively.

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

Fortunately, Microsoft Excel provides us with a function called the **OFFSET **function, which 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 on a given number of heights and a given width from that cell. In this section, I will use it to find the total sales of **Smartphones **in **2020 **and **2021**.

**ðŸ“ŒÂ Steps:**

- Initially, select cell
**D11**and write down the following formula.

`=SUM(OFFSET(B4,MATCH("Smartphone",B5:B9,0),MATCH(2020,C4:D4,0),1,MATCH(2021,C4:D4,0)))`

**ðŸ”Ž Formula Breakdown:**

- Firstly, using the
**MATCH**function, the three criteria:**Smartphone**,**2020**, and**2021**are matched with ranges**B5:B9**,**C4:D4**, and**C4:D4**, respectively, from the dataset. - Here, the match type is
**2**, which gives an exact match. - After that, the
**OFFSET**function extracts the values of the matched cells. - Lastly, the
**SUM**function provides the sum of the output values provided by the**OFFSETÂ**function.

- Finally, press
**Enter**in order to get the final output.

### 2. Combine OFFSET and MATCH Functions to Sum in Multiple Rows and Single Column

In this part, I will try to determine some sums using the **OFFSET **function of Excel along with the **SUM **and **MATCH **functions. The process is straightforward and handy to operate. However, I have combined the functions in order to find the sum for multiple rows and a single column. Hence, go through the steps below to calculate the total sales for the year **2020**.

**ðŸ“ŒÂ Steps:**

- Firstly, select cell
**D11**and insert the following formula.

`=SUM(OFFSET(B4,1,MATCH(2020,C4:D4,0),MATCH("Television",B5:B9,0),1))`

- Lastly, hit
**Enter**key to get the final output.

### 3. Join OFFSET and MATCH in Excel to Find Total in Multiple Rows and Multiple Columns

Furthermore, I have joined the **OFFSET **and **MATCH **functions to find the sum for multiple rows and multiple columns in Excel. For the purpose of demonstration, I have chosen the complete dataset and calculated the **total sales** of all products in **2020 **and **2021**. However, the process is quite similar to the previous ones. Hence, follow the steps below in order to get your desired outcome.

**ðŸ“ŒÂ Steps:**

- In the beginning, click cell
**D11**and insert the formula below.

`=SUM(OFFSET(B4,MATCH("Laptop",B5:B9,0),MATCH(2020,C4:D4,0),MATCH("Television",B5:B9,0)-MATCH("Laptop",B5:B9,0)+1,MATCH(2021,C4:D4,0)))`

**ðŸ”Ž Formula Breakdown:**

- Initially, using the
**MATCH**function, the four criteria:**Laptop**,**2020**,**Television**, and**2021**are matched with ranges**B5:B9**,**C4:D4**,**B5:B9**, and**C4:D4**, respectively, from the dataset. - Secondly, the match type is
**10**, which gives an exact match. - Thirdly, the
**OFFSET**function extracts the values of the matched cells. - Finally, the
**SUM**function provides the sum of the final values provided by the**OFFSETÂ**function.

- Lastly, press
**Enter**key to receive the final result as shown in the picture below.

### 4. Apply Excel OFFSET and MATCH Functions to Sum with Criteria

Last but not least, I applied the **OFFSET **and **MATCH **functions to calculate the sum with criteria or conditions. For this reason, I utilized the **SUMIF **function in addition to those to impose a condition. Moreover, it is suitable for several situations when the sum satisfies particular conditions. For example, I calculated the total sales for the year **2021 **to be more than **$500**. However, read through the steps below in order to complete the task easily.

**ðŸ“ŒÂ Steps:**

- First, click cell
**D11**and write the formula mentioned below.

`=SUMIF(OFFSET(B4,1,MATCH(2021,C4:D4,0),MATCH("Television",B5:B9,0),1),">500")`

Here, I have used the **SUMIF **function instead of the **SUM **function in order to impose the condition.

- Finally, press
**Enter**button in order to receive the final result.

## Conclusion

These are all the steps you can follow** to complete a sum using OFFSET and MATCH in Excel.** Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit **ExcelDemy.com**.

Good stuff!

I have underestimated Offset function for a long time

Thanks, Crispo, for your feedback.