A running total updates with each new value within a series. We use them to balance accounts, track inventory, and so much more. Fortunately, a simple expression can handle the job, even when a business rule complicates things. In this article, I’ll show you three expressions for implementing a running total.

I’m using Excel 2016 desktop (Office 365) on a Windows 10 64-bit system, but you can use all three expressions in older versions. In addition, they’re applicable to Excel’s browser version. You can work with your own data or download the demonstration .xlsx and .xls files.

SEE: Windows 10 power tips: Secret shortcuts to your favorite settings (Tech Pro Research)

1. Simplest expression

Summing values as they occur requires a simple expression that sums the current value with all those within the same series that precede it. Figure A shows a simple data set with several payables and payments. Let’s add a running total for this table as follows:

  1. Enter Balance in E2.
  2. Select E3 and enter the simple expression =D3. (There’s nothing to add to the first value, so the expression references only the one cell.)
  3. Enter =E3+D4 into E4, and then copy that expression to the remaining cells (Figure B).

Figure A

We’ll add a running total to this simple data set.

Figure B

An expression using the addition operator returns an updating balance.

If you’re working with an existing Table object, Excel will add a calculated column. You can add both expressions, but when you enter the addition expression into E4, Excel will display an error value. The calculated column updates the original expression in E3, which returns an error.

Instead, enter the addition expression into E4 first. From the AutoCorrect Options smart tag, choose Overwrite All Cells in this column with this formula (Figure C), which will return a column of #VALUE errors. Enter =D3 into cell E3 to complete the column and return the running total values.

Figure C

Overwrite the cells.

A running total won’t always be this simple. Next, we’ll look at a conditional running total.

2. Add a condition

Adding an expression that returns an updated total with every entry is helpful, but sometimes you’ll need something more specific. For instance, let’s suppose you want the total to start over at the beginning of each month. The strategy is the same, but you need a way to express the condition within the basic expression.

You could write a complex formula, but instead, let’s use a helper column. Once you see the condition in pieces, it’s easier to write a more complex formula. For my purposes, I’m fine with leaving helper columns in tact; they can be self-documenting, and often, you can reuse them with other calculations.

In this case, we need an expression that expresses the date and year. The simplest way to do this is to concatenate the month and year, as shown in Figure D. To accomplish this, enter the following formula into F3, and then copy it to the remaining cells:

=MONTH(C3) & YEAR(C3)

Figure D

Concatenate the month and year to create a value that “groups” the records by month.

Next, enter the simple expression, =D3, in G3. The rest of the job is accomplished with a simple IF() function as shown in Figure E. The following function compares the month and year combination in the corresponding cell to the one above:

=IF(F4=F3,G3+D4,D4)

When the two helper values are the same (meaning they’re in the same month), the expression sums the corresponding value and the value above. When they’re not the same (meaning the second value is returning a different month), the expression returns the corresponding value (without adding the value above).

Figure E

The IF() function compares the current Helper expression (month and year) to the one above before adding any values.

The records must be sorted by the Date column for this technique to work properly. If you occasionally sort the records, or you’re not entering them in date order, you must remember to sort the conditional component accordingly to return the expected results; the IF() function will continue to display values, but they won’t be the expected values.

Using two expressions is easy, but it’s not the most efficient solution. Next, we’ll look at a referencing trick that allows you to use only one expression.

3. A single expression

In #1 and #2, you needed two different expressions to finish the job, but you can reduce it to one by using the SUM() function and mixed referencing. Figure F shows the results of entering the following function in F3 and copying it to the remaining cells:

=SUM($D$3:D3)

Figure F

Use one expression instead of two.

As you can see, this solution requires only the one function. The trick is the creative referencing. (The error alerts warn that the expression doesn’t include the adjacent columns, which is okay in this case.) The expression uses mixed referencing to create an expanding range. The first reference in the equation is absolute. When you copy the expression, it won’t change. The second reference is relative and updates as

=SUM($D$3:D3)
=SUM($D$3:D4)
=SUM($D$3:D5)

and so on. The range extends one value for each row. Unfortunately, this expression won’t accommodate a monthly condition because the absolute reference doesn’t update with the month.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

See also