Software

How do I... Calculate a conditional running total in Excel?

<img src="http://t.cbsimg.net/i/z/200606/how_110x85.jpg" align="right" border="0" height="85" hspace="5" vspace="5" width="110" />Running totals are used to update totals within a series in Microsoft Excel. For instance, you might use a running total to track an account balance, enrolling students, or even inventory. Usually, a simple expression that adds the previous total to the latest value does the trick. Susan Harkins shows you what to do when a business rule throws a monkey wrench into the works, requiring the use of conditional running totals.

Running totals are used to update totals within a series in Microsoft Excel. For instance, you might use a running total to track an account balance, enrolling students, or even inventory. Usually, a simple expression that adds the previous total to the latest value does the trick. Occasionally, a business rule throws a monkey wrench into the works by forcing special conditions.

This blog post is also available in PDF form in a TechRepublic download, which includes a sample Excel worksheet demonstrating the techniques outlined.

Running totals

A running total is simply a sum as the values occur. In other words, a running total sums the current value and all those that precede it. Figure A shows a running total in its simplest form. The expression, which you can see in the Formula bar, adds the preceding running total to the current deposit value. The one catch is that the first cell in the series (=B2 in cell C2) must refer to the first value in the series, because there's nothing to total yet.

Figure A

A simple expression maintains a running total
Figure B shows another type of running total called a balance. In this case, the balance represents the amount of outstanding funds in a simple accounts payable worksheet. The first expression in column D anchors the total, just like the previous example, by calculating the activity in the first row. Here, the expression in cell D2, =SUM($B2,-$C2), subtracts the first credit from the first payable amount.

The running total expression starts in D3 and also subtracts credits from the payables, but it includes the previous balance. You could use the same type of worksheet to track inventory. (Typically, you probably wouldn't have multiple entries for the same date in such a simple sheet.)

Figure B

Use a running total to track deposits and credits.

A conditional running total

Within the context of your needs, a straight running total might not be at all adequate. For instance, let's suppose you want to track payables by the month. You'd still use a running total, but a conditional one.

Your first attempt at satisfying this requirement might be the SumIf() function, but there's no way to express your condition. You might try the Data Subtotals feature, but that won't work either because that feature calculates a subtotal for every entry, regardless of the month.

The key to calculating a conditional running total is discerning the condition and then expressing that condition in a way that Excel can interpret. In this case, the condition is the month. In other words, you want to add values as long as the month and year are the same. When the month or the year changes you want the running total to "start over."

The best place to start is by expressing the condition and calculation in a generic manner: If the current month and year equals the previous month and year then add the previous running total to the current value. Otherwise, use just the current value.

Expressing your needs in terms Excel can interpret is the next step. Like the previous examples, you must anchor that first value, so the first cell in column C in Figure C references the first payable value of $200.

Figure C

Every running total column needs an anchor cell that references the first value in the series.
A conditional formula that compares the current date to the previous one returns a running total, but not the one you need, as you can see in Figure D. The comparison includes the day and calculates a running total for each day, not each month. Now, that might be useful, but it isn't the right running total.

Figure D

A running total can track daily entries.
The easiest way to compare the month and year components in a single value is to add a new column that combines the two components. When the month and year are the same, the combined value will be the same. Figure E shows just such a value — the concatenated result of each date's month and year component.

Figure E

Combine the month and year value to create a comparison value.
Figure F shows a monthly running total in column D. The expression is essentially the same as the previous one in structure. However, instead of comparing the actual date values in column A, it compares the concatenated values in column B. As long as the value in column B is the same, the expression adds the previous running total to the current payable value.

As you can see, the running total adds payable values until the month changes in row 5. Because the value in column B is different from the previous row the expression returns just the current payable value — in effect, the running total starts over.

Figure F

Compare more and year values to track monthly entries.

Run total run!

Tracking values as they occur requires a bit of special attention when you place conditions on the running total. Solutions to calculating a conditional running total will be unique to every situation. The key is to express the condition in terms you can understand and then translate that statement into terms Excel understands.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks