We can use the OFFSET function to create dynamic ranges in SUMIF formulas in Google Sheets. In this tutorial, we will learn how to do that.

By “dynamic ranges,” I mean ranges in a formula that automatically adjusts to include values in any rows inserted above or below the original range.

If you want to create a dynamic sum column without encountering the ‘argument must be a range’ error, please check out: Dynamic Sum Column in SUMIF in Google Sheets.

How to Apply Dynamic Ranges in the SUMIF Formula in Google Sheets

Let’s follow the steps below to learn this.

1. Sample Data and Basic SUMIF Formula
2. Creating a dynamic range for the SUMIF formula
3. Formula Explanation

1. Sample Data

Here is my sample data for learning how to use dynamic ranges in a formula.

You can copy my sheet with the sample data by clicking the button below to follow the instructions step-by-step.

Sample Sheet

Basic SUMIF Formula:

=SUMIF(D2:D8, "Safety Helmet", H2:H8)

In this formula, D2:D8 is the range, “Safety Helmet” is the criterion, and H2:H8 is the sum range.

Syntax: SUMIF(range, criterion, [sum_range])

Result: 120.00

Now, let’s see what happens when you insert new rows above and below our selected ranges in the SUMIF formula.

The formula changes the ranges from D2:D8 to D3:D9 and H2:H8 to H3:H9. It excludes the newly added rows.

It uses relative range references. You can try using absolute range references in the formula by replacing D2:D8 and H2:H8 with $D$2:$D$8 and $H$2:$H$8, respectively. However, the behavior will remain the same.

2. How to Create a Dynamic Range for the SUMIF Formula Above?

We can use OFFSET-based dynamic ranges in SUMIF to overcome the mentioned problem in Google Sheets.

In the given SUMIF formula, instead of the range D2:D8, we can use the following OFFSET formula:

OFFSET(D1, 1, 0):OFFSET(D9, -1, 0)

For H2:H8, the alternative offset range is:

OFFSET(H1, 1, 0):OFFSET(H9,-1, 0)

Now, let’s apply dynamic ranges in the SUMIF formula in Google Sheets. Here’s the modified formula:

=SUMIF(OFFSET(D1, 1, 0):OFFSET(D9, -1, 0), "Safety Helmet", OFFSET(H1, 1, 0):OFFSET(H9, -1, 0))

Using this formula eliminates the need to manually adjust the formula range.

When you insert new rows just above or below the formula range, they will be automatically included in the calculation.

3. Explanation of the SUMIF Dynamic Range Formula

Before delving into the formula explanation, let’s observe the behavior when new rows are inserted above and below our data range.

In our example, the dynamic SUMIF formula utilizing the OFFSET function yields the correct result in cell H14. Conversely, the standard SUMIF formula in cell H13 overlooks the inserted rows during calculation.

There’s nothing particularly intricate about this dynamic formula. We’re simply substituting the ‘range’ reference with an OFFSET formula.

Let’s review the syntax of the OFFSET function:

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

Now, let’s revisit our basic/standard SUMIF formula and its associated ranges:

=SUMIF(D2:D8, "Safety Helmet", H2:H8)

In the dynamic SUMIF formula using OFFSET, D2:D8 is replaced by the following formula:

OFFSET(D1, 1, 0):OFFSET(D9, -1, 0)

Here, within the OFFSET function, we’ve employed D1 as the cell reference with an offset of 1. Consequently, the cell reference effectively becomes D2.

The advantage here is that you can insert any number of rows just above D2, and the OFFSET function will dynamically adjust to include them in the SUMIF calculation.

Similarly, for the upper bound of the range (D8), in the dynamic SUMIF using OFFSET, it’s adjusted to D9 with an offset of -1, ensuring the actual cell reference remains D8.

These principles also apply to the range H2:H8.

Leveraging Structured Table References for Dynamic Ranges in SUMIF

If you’re using a Table, you don’t need to resort to the OFFSET method for dynamic ranges in SUMIF or any other formulas.

Here are the steps to convert a range to a table:

1. Select the range A1:H8 (please scroll up and see the sample data).
2. Click on Format > Convert to Table.

Formula based on our sample data:

=SUMIF(Table1[Item Description], "Safety Helmet", Table1[Amount])

You might need to replace the table name in this formula, which is Table1. You can find the table name at the top left corner of the table.

Related: Structured Table References in Formulas in Google Sheets

When you insert any row below the header row, the formula will dynamically include it, as it uses structured table references. Similarly, if you enter any value below the table range, the table will expand, and that row will also be included in the calculation.

Resources

• REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets.
• Multiple Sum Columns in SUMIF in Google Sheets.
• How to SUMIF/SUMIFS Excluding Duplicates in Google Sheets.
• Sumif Importrange in Google Sheets – Examples.
• The Sum of Matrix Rows or Columns Using Sumif in Google Sheets.
• How to Use SUMIF in Merged Cells in Google Sheets.