You can use dynamic Ranges in Google Sheets formulas with the help of the OFFSET function. By saying Dynamic Ranges, I mean the range in a formula that accommodates values in any rows that inserted later, that just above or below the formula range. So, this time we can learn how to use Dynamic Ranges in SUMIF formula in Google Sheets.
How to Apply Dynamic Ranges in SUMIF Formula in Google Sheets
I’m going to adopt the following methods to explain Google Sheets Dynamic Ranged Formulas.
- Sample Data.
- Basic SUMIF formula.
- How to Create a Dynamic Range for the SUMIF formula above?
- Formula Explanation.
1. Sample Data
Here is my sample data for learning the dynamic ranges in formula.
2. Basic SUMIF Formula
Here is the basic SUMIF formula. In this formula D2:D8 is the range, “Safety Helmet” is the criterion and H2:H8 is the Sum Range.
Formula =sumif(D2:D8,”Safety Helmet”,H2:H8)
Now see what happens when you insert new rows above and bottom of our selected ranges in SUMIF.
The formula changes the ranges from D2:D8 to D3:D9 and H2:H8 to H3:H9 that definitely excludes the newly added rows in the range. You can try using Dollar Symbol to freeze the rows as well as columns similar to copy and paste. But that doesn’t affect inserted rows.
3. How to Create a Dynamic Range for the SUMIF formula above.
To overcome the above scenario, we can use Offset Dynamic Ranges in Google Sheets. In the above SUMIF formula, instead of the range D2:D8, we can use the below offset formula.
For H2:H8, the alternative offset range is;
Now you can see how I’m going to apply Dynamic Ranges in SUMIF formula in Google Sheets. Here is that formula.
If you use this formula in the above example, there is no need for you to change the formula range manually. It would automatically adjust the data ranges when you insert new rows above or below the formula range.
4. Formula Explanation.
Before going to the formula explanation part, here see what happens when I insert new rows above and below our data range.
Here our dynamic SUMIF formula using Offset as range, returns the correct result in cell H14 while the normal SUMIF formula in cell H13 skips the inserted rows in calculation.
Actually there is nothing special in this dynamic formula. Here you are just replacing the range with offset. See the Offset syntax.
OFFSET(cell_reference, offset_rows, offset_columns
Once again see our basic SUMIF formula and ranges.
In SUMIF using Offset as ranges, D2:D8 is replaced by below offset formula.
See instead of D2 in basic SUMIF, here I’ve used D1. So D1 is the cell reference and 1 is the number of rows to Offset. So actually here also the cell reference is D2. So you can insert any number of rows above D2. Offset would include this in the dynamic ranges.
Similarly, in SUMIF, another cell reference is D8. But in dynamic SUMIF using Offset, it’s D9 and Offset -1. So again here also the cell reference is D8.
If you still have doubt, I suggest you to follow my below tutorial where I’ve explained the use of Offset function and how to use it dynamically with the help of examples and images.