# INDIRECT Sum – Excel & Google Sheets

Written by

Reviewed by

Download the example workbook

*This tutorial will teach you how to use the INDIRECT Function nested in the SUM Function in Excel and Google Sheets.*

## INDIRECT Sum Using Cell Reference

One way to sum a dynamically referenced range is by using a combination of the SUM and INDIRECT functions.

This example will SUM the defined range in cell F5.

`=SUM(INDIRECT(F5))`

## INDIRECT Sum Using Dynamic Sheet Reference

To sum a range dynamically referenced from another worksheet, use a similar formula:

`=SUM(INDIRECT(B3&"!"&C3))`

The difference here is that we also must define the sheet name.

## INDIRECT – Volatile Function

**Warning:** The INDIRECT Function is volatile. This means it re-calculates every time the workbook re-calculates. If you rely on too many INDIRECT functions, it will negatively impact your workbook speed.

Alternatively, you can use other non-volatile functions. One such function is the OFFSET Function.

## Dynamic SUM using OFFSET Function

The OFFSET Function returns a reference to a range that is offset from a starting cell or range by a specified number of rows and columns. It can be nested in the SUM Function to dynamically reference a range and sum values.

The following example uses the OFFSET Function to sum range ‘**D3:D7**‘.

`=SUM(OFFSET(B2,1,2,5,1))`

Starting with cell B2, the Function offsets one (1) row and two (2) columns to obtain the range D3:D7 with a height of 5 and width 1.

If the last two arguments ‘**[height]**‘ and ‘**[width]**‘ are omitted, the new reference will have the same height and width as the starting reference.

Note: The OFFSET Function allows formulas to dynamically adjust to the data. Therefore, an addition or deletion of rows and columns would not cause the final output to change.

## INDIRECT Sum in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.