Download Practice Workbook
Download the workbook.
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
Calculate the total sales of all products in 2020 and 2021.
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
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!
Good stuff!
I have underestimated Offset function for a long time
Thanks, Crispo, for your feedback.