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.

27 comments
iansavell
iansavell

On the sumif() point, simce my PC became effectively infinitely powerful I've begun to change my view of formulas. I sometimes use profligate formulas to avoid common sources of error. This includes avoiding things like "anchor formulas". Take the sheet in figure E. In D2 put =SUMIF(B$2:B2,B2,C$2:C2) and copy all the way down. The anchor is now in the formula, which is the same in every row including the first. This "floating range" technique works nicely in more complex scenarios where the criterion column may be unsorted

ewingdweller
ewingdweller

In the final spreadsheet, you can display the "B" column as Year & Month by using the following formula: =CONCATENATE(YEAR(B5),IF(MONTH(B5)

collinsauve
collinsauve

Definitely not worth the top spot in the newsletter.. how about indirects, sumproducts, indices or webqueries... those are the really interesting and tricky ones

bjbesaw
bjbesaw

If you add the extra column with the year/month combination, then you are back to your orignal point that you could just use sumif() and be done with it.

teesq
teesq

Re: Date logic. The following issue is not applicable to Month and Year concatenation situations. If you need to compute totals based on day values, you should use fixed length date strings, ie. A 2-character fixed month and day value format. (Eg. '20080111' is more precise than '2008111' which could mean either Jan 11 2008 or November 1 2008).

scotlee
scotlee

very helpful, thank you!

deonvn
deonvn

Without adding the extra column with the concatenation, the (more complex) formula =if(and(month(A3)=month(A2),year(A3)=year(A2)),D2+C3,C3) will do the trick. This however underlines the constant balancing between comlexity and readability on the one hand vs extra columns Deon

ssharkins
ssharkins

Thanks for supplying a great enhancement to the technique!

peter.kearney
peter.kearney

You can avoid typing out =CONCATENATE(A,B) by using A&B, where A and B are cell references or functions such as (YEAR(B5).

alaniane
alaniane

Instead of "=CONCATENATE(YEAR(B5),IF(MONTH(B5)

ssharkins
ssharkins

The one thing I've learned is that there's almost always more than one way to accomplish something. I choose this technique because it's so simple -- and is really more about thinking through the condition so you can find away to state it. I prefer an easy-to-implement solution than an "interesting" or "tricky" one. However, sometimes you really need a trick or two to get the job done.

peter.kearney
peter.kearney

Do you fancy telling us about indirects, sumproducts, indices and webqueries! Sounds interesting. I agree conditional running totals pretty basic. As you probably know most Excel users struggle with formulae other than =sum, however, and this is something that most regular users would find useful.

Prendo
Prendo

I'm with Deon I've found that learning the "complicated" way is not (after all ) that hard, and allows you to more readily deal with issues like the one from teesq. James

ssharkins
ssharkins

That's a keen observation and definitely might muck things up in your months aren't in consecutive order -- good insight.

ssharkins
ssharkins

I think complex formulas are fine. It's really up to the user. I used an additional column because the visual picture was so helpful. Thanks for sharing the more complex formula, for those that might want to use it, instead.

LocoLobo
LocoLobo

would be to hide the extra columns or move them over a couple of pages. That way it stays readable. Your formula makes sense though.

ssharkins
ssharkins

I'd like to invite you to post alternate conditional running total solutions. I'm all for letting users decide for themselves -- let's give them all the tools they need.

ssharkins
ssharkins

I tend to not hide columns unless I'm printing, but that's just a personal druther. You are absolutely right -- you can hide the intermediate columns if they're in the way.

dpf18
dpf18

The formulae presented do get complex after a while. You could, instead, use a named formula. The process is similar to naming a cell range except that you enter a formula instead of a cell range! Many Excel users don't know this "trick."

LocoLobo
LocoLobo

My spreadsheet design process is similar to how the article outlined it. Start with the raw data then intermediary formulas then on to the final results. At this point if it makes sense I attempt to combine formulas. That depends on what the customer/user wants to see. Usually my users don't want to see all the intermediate numbers though. Rather than hide them I usually move them to the side so that they're not in view. The boss has his printing preferences. Our linear regression analyses look almost exactly the same as the ones done on a spreadsheet in the 70s using calculator and pen.

peter.kearney
peter.kearney

I too pride myself in my Excel skills. Ive been doing running totals since Lotus 123 but this is totally awesome. I'm not sure that it enhances the readability though - its sure to bamboozle most. Not to mention the use of array formulae in that context. Full marks to the dude who thoought of creating this.

john.furstenwerth
john.furstenwerth

I knew about named formulas, but didn't know about the Excel Oddities site. Just now learned about DATEDIF there, along with some other good links.

andrea1211
andrea1211

Sounds very interesting. I think I'm a pretty experienced excel user but I've never used a named formula. Can you provide an example of how to use it?

Editor's Picks