In the below demonstration we will use custom named ranges for numerical values in one column to create a dynamic named range with the SUM function.
1. Click “Data” in the File menu followed by “Named ranges”
To create a dynamic named range, in the File menu click Data and from the dropdown menu click Named ranges.
2. Click “+ Add a range” in the sidebar and customize parameters
In the side bar that appears on the right, click + Add a range and two new textboxes will appear.
Assign a name for your named range (this must not include spaces).
Set the cell values manually or by clicking the symbol ⊞ and selecting the first cell, dragging and highlight the rest of the required cells.
In the example below we will name this as Q2_Data_2 and select cells B2:B covering the start of the data set minus the header and covering the rest of the entire column.
If you manually selected a cell range like B2:B13, you need to also change it to B2:B. This will ensure that any additional entries made in column B are included in this named range and what makes it a dynamic range.
Press the Done button to complete the named range.
3. Click an empty cell and enter the custom formula =SUM(Named_Range)
Click on an empty cell and enter the formula in the format of =SUM(Named_Range)
=SUM: This function is used to add together a list of numerical values.
(Named_Range) This is the dynamic named range created via the Data option in the file menu (In our example below our named range ‘Q2_Data_2’)
In our example we will enter this formula in cell F7:
As you begin typing your dynamic named range, google will autosuggest the full name.
4. Press Enter to see the results
The Dynamic named range is now displayed. Any additional values entered in the B column will automatically be reflected in our dynamic range box.