Formula for Automatic Summing When Adding New Rows in Excel

Excel is popular for its ability to quickly and automatically calculate complex formulas. Mastering basic commands can save a significant amount of time.

The OFFSET function belongs to more complex array functions, so it is used less often. However, it can be very useful when calculating a sum, especially when new rows need to be added.



Examples of Auto-Sum Formula in Excel

Here's a formula for automatically summing cells in a column when adding or deleting new rows. The formula uses two functions: SUM and OFFSET.

Example of the OFFSET Function and Its Argument Syntax

This function returns a reference to a range that is offset from a given reference by a specified number of rows and columns. The definition may sound unclear to an average user. Here’s the parameter syntax:

1. Reference: This is the starting cell. The offset will be based on this cell.
2. Rows offset: The number of rows to move up or down from the starting cell. A positive number (from 1 onward) moves down, and a negative number (from -1 onward) moves up.
3. Columns offset: The number of columns to move left or right from the starting cell. A positive number moves right, and a negative number moves left.
4. Height and width: These parameters define the range that the OFFSET function will return. If you need a single cell, enter 1 and 1. If you need a table, enter other values like 3 and 4, or 6 and 8, etc.

The OFFSET command has several uses, but today we’ll focus on its most common and practical one—automatic summing of new values from newly added rows.

Example of Using the OFFSET Function

For those new to the OFFSET function, it may initially seem like it can be replaced by the familiar SUM function. However, in some cases, SUM won’t work. Let’s break this down with a specific example.

A company is creating a database of prices for various brands of toothpaste purchased at different wholesale rates. At the bottom of the table, the total amount to be paid for each unit is displayed. While there are no new products in stock, the table remains unchanged, and SUM works fine.

But when the company adds a new toothpaste brand (let's call it M), it needs to add additional rows. If you do this and enter a new number, SUM won’t respond to the new row (indicated by a green triangle in the top left corner of the cell and an exclamation mark nearby), because SUM was set for a specific range. And we see that the total remains the same, = 554.

It's important to write the OFFSET formula correctly to ensure it works. Here’s the step-by-step process. Our goal is to calculate the sum, so SUM will be the outer function.

The first value in SUM will be a reference to the initial cell where the calculation starts, D5. After that, we enter the OFFSET command, which will be inside the SUM function.

We begin filling in the OFFSET function syntax. The first parameter is the reference, which will be the cell where our formula is located, D18.

The rows offset will be -1, so that the second element in the summing range is always in the previous cell.

We don’t need a columns offset, so we either enter 0 or leave it blank. Remember to separate the sum formula arguments with a colon.

We won’t specify height or width, as the default will be a single cell.

After these steps, press Enter and get the result. The OFFSET function has summed not only the defined range but also the newly added row. Let’s try adding another row to test the formula. Suppose this is toothpaste brand N, priced at 51 rubles.

And we see that the total increased by 51 (now 650), while the SUM function remains the same (554).

This formula automatically sums values in a column when new rows are added, and also when rows are deleted. It’s important not to delete the first row referenced by the first argument in the sum formula.

Download example of auto-sum formulas for adding new rows in Excel

OFFSET responded perfectly to the addition of new rows. You can continue adding values at any time, and the table will keep working.

Learning this function, which allows you to automatically add new values to the total, usually takes no more than an hour. However, in the future, it will save significant time at work. We reviewed a simple example with small numbers, but many companies work with large data sets, and using SUM—or worse, a calculator—would be unwise.