2 Criteria for Summing with Indirect and MATCH in Excel

If you work with Excel, you know how important it is to be able to quickly and easily sum data in a spreadsheet. While there are many ways to do this, using the SUM function in combination with the INDIRECT and MATCH functions can be a powerful tool. In this article, we'll go over two criteria for summing with indirect and match in Excel, and provide some examples to help you get started.

What is the INDIRECT function?

The INDIRECT function is used to return a reference to a cell or a range of cells from a text string. This can be useful when you want to create a dynamic reference that can change based on the value of another cell or a user input. For example, you could use the INDIRECT function to create a reference to a cell in another worksheet, or to create a reference to a range of cells based on a user-defined range.

What is the MATCH function?

The MATCH function is used to find the position of a specific value in an array or a range of cells. This can be useful when you want to find a specific value in a list or a table, and then use that value to perform some other action. For example, you could use the MATCH function to find the position of a specific product in a list of products, and then use that position to look up the price of that product in another table.

Criteria for Summing with Indirect and Match in Excel

Now that we've covered the basics of the INDIRECT and MATCH functions, let's look at two criteria for summing with indirect and match in Excel:

1. Summing data from multiple worksheets

The first criterion for summing with indirect and match in Excel is summing data from multiple worksheets. To do this, you can use the INDIRECT function to create a reference to a range of cells in another worksheet, and then use the SUM function to add up the values in that range. Here's an example:

=SUM(INDIRECT("Sheet2!"&ADDRESS(MATCH("Total",Sheet1!A:A,0),2)))

In this example, we're using the MATCH function to find the position of the word "Total" in column A of Sheet1. We're then using the ADDRESS function to return the address of the cell in the second column (column B) of the same row. Finally, we're using the INDIRECT function to create a reference to the range of cells in Sheet2 that corresponds to the address we just calculated. We're then using the SUM function to add up the values in that range.

2. Summing data based on a user-defined range

The second criterion for summing with indirect and match in Excel is summing data based on a user-defined range. To do this, you can use the INDIRECT function to create a reference to a range of cells based on a user input, and then use the SUM function to add up the values in that range. Here's an example:

=SUM(INDIRECT(B1&":"&B2))

In this example, we're using the INDIRECT function to create a reference to a range of cells based on the values in cells B1 and B2. We're then using the SUM function to add up the values in that range. This allows the user to define the range of cells to be summed by entering the starting and ending cell references in cells B1 and B2.

In this article, we've covered two criteria for summing with indirect and match in Excel: summing data from multiple worksheets, and summing data based on a user-defined range. By using the INDIRECT and MATCH functions in combination with the SUM function, you can create powerful and flexible summing formulas that can save you time and improve your productivity. So, give it a try and see how it can help you in your work.

References