In Google Sheet, if you wish to capture a data range as it shrinks or expands, you would apply the concept of dynamic range.
Why Use a Dynamic Range
Google Sheet functions such as SUMIF() and Subtotal() can be applied to a dynamic range to yield the correct result. This is often useful when you have items listed by category and you want to get the total for each category. This way you do not have to adjust the range to sum for each category as you add or remove items in that category.
Dynamic Row Range
This post will address vertical dynamic range that is bound by a blank cell below. The principle can be applied to horizontal ranges across columns.
There are several functions that allow you to specify the range, such as Index(), Indirect(), Offset(). This post will examine the use of Indirect() with dynamic Match() function for SUMIF analysis and Indirect() with Row() function for subtotal.
Indirect() Function with Dynamic Match()
Suppose you want to slice and dice the data by Type column, which describes the nature of posts on the platform in one word, either “Image” or “Word”.
SUMIF() function can be used, which has the syntax
SUMIF(range, criterion, [sum_range])
Here, the “range” parameter is actually the criteria range, or the range that contains your criteria.
Column F above shows the sum of whole column irregardless of whether blank cells are present. Formula is cell F3 is
The specification “$C2:$C” means Google Sheet will include the whole range in column C except cell C1. So if you subsequently add more data in column B and C, this will be reflected in cell F3.
The second parameter specifies the criteria you want to find in the first parameter’s range. For cell F3, that would be where Type column has the word “Image”.
The third parameter is the range to sum, which is the Visitor column, or Column B.
Cell F5 (Total Visitors) is as simple as
Exclude Data Range After a Blank Row
Now suppose you want to sum only the range that is above a blank cell, namely rows 2 to 4 but not rows 5 and 6.
You can use the same SUMIF() function with adjustment to criteria range and sum range.
Cell G3 in the above example has the formula
While this may look intimidating, it is actually only the application of Indirect(), Arrayformula() and Match() for both criteria range and sum range.
To specify where criteria range would end, a Match() function is used to find the position of the first blank cell in the range B2:B6.
Now ISBLANK() function is not an array function that can produce multiple answers, so unless “match(true, isblank(B2:B6),0)” is wrapped in Arrayformula() function, the whole formula will not work.
The part “arrayformula(match(true, isblank(B2:B6),0))” produces the result “4”, which means the last row of interest is row 4 in this context.
So the statement inside Indirect() function gives “B2:B4”.
The Indirect() function converts the text reference “B2:B4” to an actual range.
Subtotal a Google Sheet Dynamic Range
If you use a simple SUM() function to do subtotalling of items there are two issues:
- If a row is inserted outside of the original sum range, the function will still refer to the original range and not product the correct result when data is added to the newly inserted row(s).
- When you copy the formula to another subtotal field, you need to change address reference.
The first point is usually not an issue if you insert rows between the original summed range. Google Sheet will take the new range into account. So if you have the formula
and subsequently add two rows between row 2 and row 4, the formula will be updated to
The second point can be addressed effectively using a dynamic range.
To create a dynamic subtotal as in the screenshot above, the formula in cell B6 is
Row() function gives the row a cell is currently in
“$B$2:$B”&row()-1 gives $B$2:$B6
Indirect() function converts address reference to an actual range
Making Formulas More Dynamic
The formula as shown here still requires you to change the starting position from B2 to B9 as you copy it to cell B12 for the subtotal of account payable.
If you want the formula to be even more flexible by taking into account the row to start the subtotal from, you can put in cell B12 the formula
filter(row(A1:A12),A1:A12=”Subtotal”) is a vertical array with values 6 and 12, representing the rows with “Subtotal” wording.
countif(A1:A12,”Subtotal”)-1) tells the number of “Subtotal” rows in the range less one, as we want to use this number to extract from the filtered array.
Index portion of the formula allows the extraction of value from the filtered array. A “1” is added to it to give the row just below the previous “Subtotal” cell, which is in A6.
Note that the formula will give 0 if you apply it to cell B6 and adjust the range in the formula to A1:A6. Therefore only apply this formula to subsequent subtotals only.
You will need to adjust the reference to cell A12 above to instead use
This significantly increases the length of the formula and make the formula much harder to read. It is the downside of using a dynamic range.
The use of Indirect() function is the focal point of the topic on dynamic range in this post, together with functions such as dynamic Match() function and Row() function.
Keep in mind that these are not the only functions you can use, and you may like to explore other similar functions such as Offset() function and Index() function when considering your choices.
There are upsides and downsides to using dynamic range in Google Sheet. If you want users to avoid headaches by allowing a range to expand or shrink at will, you will also need to take into account of whether your formula is robust enough such that users do not need to do their own error handling.
Dynamic formulas can also be quite long, making them harder to understand and debug.