How to Use INDIRECT with SUM to Create Dynamic Sum Formulas
By use INDIRECT with the SUM function, you can easily create dynamic sum formulas that refer to cell ranges specified as text. This allows you to avoid changing the reference from the formula multiple times.
Use INDIRECT, you can create a cell or range reference by entering text into it. Here are the steps to combine use INDIRECT with SUM:
- Enter the cell range you want to refer to in a cell.
- In a different cell, enter the SUM function.
- Next, use the INDIRECT function and refer to the cell with the range address in the first argument of use INDIRECT.
- Close both functions and hit enter to get the result.
When you hit enter, the formula will return the sum of the values from the specified range, such as A2:A7.
Using INDIRECT to Sum Data from Another Sheet
If the range you want to sum is on a different sheet, you can still use INDIRECT and SUM.
For instance, you can enter the formula in “Sheet2” and refer to the range from the “Data” sheet. In the formula, enter the sheet’s name in one cell and the range itself in another cell. Then use this formula:
=SUM(INDIRECT(A1&”!”&B1))
Using INDIRECT to Sum Data from Multiple Sheets
If you want to sum values from a range of multiple sheets, you need to use a formula that employs SUMPRODUCT and SUMIF instead of SUM. Here’s an example formula:
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&A2:A4&”‘!”&B2),”>0″))
In this formula, INDIRECT refers to the name of the sheets and the range, creating a 3D-Range for the A2:A7 range in three sheets (Data1, Data2, and Data3). 
Then, SUMIF uses this range and returns individual sums from all three ranges. 
Finally, SUMPRODUCT adds up the values and returns a single sum value in the cell.
To learn more about using SUMPRODUCT IF and its usage, you can refer to this guide.
