Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Sum Using OFFSET and MATCH in Excel (4 Ideal Examples)

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.

Sample Dataset to Calculate Sum Using OFFSET and MATCH in Excel


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

Sum in Single Row and Multiple Columns Using OFFSET and MATCH in Excel

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

Combine OFFSET and MATCH Functions to Sum in Multiple Rows and Single Column in Excel

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

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

🔎 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")

Apply Excel OFFSET and MATCH Functions to Sum with Criteria

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.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. Good stuff!

    I have underestimated Offset function for a long time

Leave a reply

ExcelDemy
Logo