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