In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.

This may appear to be difficult in some cases. Take the example below. A file with all the examples can be downloaded using the button at the bottom of this post.

Column D adds up the Month to Date (MTD) Sales using the following formula in cell D2.

=SUM($B$2:B2)

The $B$2 reference doesn’t change as the formula is copied down. The second B2 does change, which causes the range to expand as the formula is copied down the column. This formula can be copied down the whole column. Goal achieved.

Column E is a little different. We can’t add up the values because we need to perform a calculation for the margin value. In the image below I have shown the most common solution.

In this example cell E2 has a different formula to E3. The formulas from column E are listed in column H.

If you copy cell E3 to E2 the following errors occur.

You can’t add cell C1 because it has text in it, which causes the #VALUE! error.

The are two solutions to this problem. There are two more solutions that don’t involve adding the previous row.

Adding text

As we saw adding text cells causes a #VALUE! error. But at least two functions don’t return an error when used with a text cell. The good ol’ SUM function ignores text when adding up values. We can create a single formula solution as below.

The SUM(E1) part of the formula returns zero. Goal achieved.

There is a more obscure function that does same thing. It is the N function. It returns numbers or zero for text. The N version is shown below.

N(E1) also returns zero. Goal achieved.

The cell D2 formula didn’t refer to other rows. We can do a similar calculation in column E using a more advanced function. See image below.

The SUMPRODUCT function multiplies two range together and adds up the results of the multiplications. (Product is a mathematical term for multiply. To get the product of two numbers you multiply them together.)

The first cell from each range is multiplied together, then the second, then the third etc. SUMPRODUCT then totals all the multiplication results to return a single value.

In the subscription version of Excel (and only the subscription version at the moment) you can do pretty much the same calculation in a SUM function – see image below.

This is an example of a dynamic array formula that is only possible with the subscription version. it does the same calcaution at the SUMPRODUCT becuase the put the * between the ranges.

As you can see there a few ways to achieve the same thing in Excel. The aim is to have a consistent formula in every cell of a column in a table. Chose the one that makes the most sense to you.

Download example file