# Using the Sum, the OFFSET and MATCH Functions in Excel – 4 Examples

The sample dataset showcases the sales record of a company.

### Example 1 – Sum in a Single Row and Multiple Columns Using the OFFSET and the MATCH functions

Find the total sales of Smartphones in 2020 and 2021.

Steps:

• Select D11 and enter the following formula.

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

Â Formula Breakdown:

• With the MATCH function, the three criteria: Smartphone, 2020, and 2021 are matched with ranges B5:B9, C4:D4, and C4:D4.
• The match type is 2: an exact match.
• The OFFSET function extracts the values of the matched cells.
• The SUM function sums the output values.
• Press Enter to see the final output.

### Example 2 – Combine the OFFSET and the MATCH Functions to Sum in Multiple Rows and a Single Column

Calculate the total sales for 2020.

Steps:

• Select D11 and enter the following formula.

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

• Press Enter to see the final output.

### Example 3 – Using the OFFSET and the MATCH functions to Find the Total in Multiple Rows and Multiple Columns

Steps:

• Select D11 and enter the following formula.

`=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:

• With the MATCH function, the four criteria: Laptop, 2020, Television, and 2021 are matched with B5:B9, C4:D4, B5:B9, and C4:D4.
• The match type is 10: an exact match.
• The OFFSET function extracts the values of the matched cells.
• The SUM function sums the final values returned by the OFFSETÂ function.
• Press Enter to see the final result.

### Example 4 – Applying the Excel OFFSET and MATCH Functions to Sum with Criteria

Use the OFFSET,Â MATCH and SUMIF functions.

Calculate the total sales for 2021 for values greater than \$500.

Steps:

• Select D11 and enter the following formula.

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

The SUMIF function createsÂ the condition.

• Press Enter to seeÂ the final result.

<< Go Back to Excel SUM Function | Excel FunctionsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio