Software

Calculate a conditional running total in Excel

Running totals

Running totals are used to update totals within a series in Microsoft Excel. 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.

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. This screenshoot 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.

This gallery is also available as a TechRepublic How do I Blog post. Image created by Susan Harkins for TechRepublic.

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
jvp@CTFolk.com
jvp@CTFolk.com

Excel has been a source of huge financial disasters when rows are added, deleted, or moved, throwing off the relative addressing and producing false totals that are frequently overlooked. The consequences can be major. By using the Indirect and Row functions, you can completely eliminate this peril, and it's a pretty simple technique. For instance, the example =D2+B3-C3 calculates a running total, adding income in B and subtracting expenses in C. The tricky one is D2: that's the one that will go wrong if the placement of Row 3 is changed. (And if Row 2 is deleted, the whole formula will have to be repaired to deal with the #REF# error.) So instead of D2, use INDIRECT("D"&row()-1), which in Row 3 will give you D2, but if you move the row it will ALWAYS give you D in the previous row. I cannot estimate all the time and worry this technique has saved me. This points up the need for an Excel function that would do what the above Indirect statement does without having to go all through that. Running totals are common enough, and screwed up running total formulas dangerous enough, to justify a function to do this. Anyone know a Microsoft Excel product manager?

bbutz
bbutz

It is much simpler and cleaner to write the formula =sum(B2:$B$2) and just copy it down. This will make one end of the sum fixed on the first cell and the other end a variable that moves down as you copy it down.

uribe.richard
uribe.richard

A much easier way to sum by month is to change the formatting of the date column, then use data subtotal to subtotal the column of interest everytime the month changes. Accomplish as follows: Anchor cursor on date column, Click Format > Cell > Number > CUSTOM. In the Type box, look for mmm-yy. If does not exist, enter mmm-yy in the data entry field. Click on mmm-yy. Click OK. Sort your range by date column. Data Subtotal as described. END

PeterTECHNO
PeterTECHNO

This formula in cell C4, "=$C3+$B4", is dependent upon another formula. It is better to use the source data whenever possible. "=Sum(B$2:B4)" would be a more concise formula. It includes only source data, which eliminates a level of complexity in the formula.

jhecker
jhecker

Image 2: Why put a simple calculation inside a Sum function =Sum($D2;$B3-$C3) Why not simply =D2+B3-C3. Further, in all of your formulae, why confuse people with the Dollar sign which suppresses column reference change in a horizontal copy when the formula isn't going to be copied? Keep it simple if you are trying to teach. Signed: 25 year Professional Trainer

pkrishna43
pkrishna43

This appears to be too elementary to be posted.

kirk.bare
kirk.bare

Conditional Array Formulas will also work and allow the formulas in the entire row to be the same, if you prefer constant formulas. For example in column C: you could use the array formula {=SUM (IF(YEAR($A$2:$A2) * 100 + MONTH($A$2:A2)=YEAR($A2)* 100+MONTH($A2), $B$2:$B2)) }. In Column D: the formula would be: { =SUM(IF( $A$2:A2=$A2, $B$2:$B2)) } In Column F: the formula doesn't need to be an array, but can be made consistent by using: =SUM($B$2:B2)-SUM($E$2:E2). (I hope the formulas copy correctly, I have always had a problem pasting formulas into this forum).

wolfjh
wolfjh

Here is a better representation of Year/Month: Year(A2)*100 + Month(A2). This allows for sorting on Year/Month (producing true sequences).

tolmr
tolmr

a PDF download of this? It is truly excellent, but I want to be able to refer to it later... Thank you.

laura.walker
laura.walker

Whether you use: =YEAR(B4)*100+MONTH(B4) ; or: =YEAR(B4)*10000+MONTH(B4)*100+Day(B4) ; or: MicroSoft's DateCode ; or: MicroSoft's Custom format mmm-yy ; or: MicroSoft's Custom format yyyymm ; or: MicroSoft's Custom format yyyymmdd ; Subtracting dates 1/3/2008 from 1/15/2008 will yield the same 12 days difference answer in all but the first equation. But I agree that sort is primary and Custom format mmm-yy would be a poor choice. Interesting comments this round. LRW

kirk.bare
kirk.bare

I may have missed something in your post. But, changing the format for 1/3/2008 and 1/15/2008 to Jan-08 only changes the format. The values are still 39450 an 39462 respectively. So when comparing to see if the dates are in the same month Jan-08 (39450) is not equal Jan-08 (39462). Or maybe there is an excel function that allows comparisons and operations on Formated Values (I use 2003, so 2007 and 2010 may have this feature, I don't know).

markbeckstrom
markbeckstrom

I have always found it to be good practice to anchor formulae when I know that the reference should never change. It is too easy to later on to modify a spreedsheet that has grown in size, by moving things around etc, and mess up my work. So it IS good training to get in the habit of using the $ sign even in basic spreadsheets. From a training perspective, using SUM just makes it very clear what is happening. No big deal either way. Signed: 30 year computer user and part time trainer

markbeckstrom
markbeckstrom

Did you look at all 6 pages? If this was too elementary WOW you must be a real expert. I my self found it to be very informative.

jy76
jy76

It's not better but in case it's useful to someone, you could enter =Year(A2) & Text(Month(A2),"00") for your sortable month/year code. This formula returns a non-numeric expression. Also, the second part of the formula is useful for showing months as two character codes (e.g. "01" for January instead of "1").

jody.burton
jody.burton

While I, too, prefer Year/Month to Month/Year, I don't see any benefit from multiplying the year by 100. Seems like an unnecessary calculation.

jrevier
jrevier

I am a bit confused as to what you are trying to say. care to explain?

Ron_007
Ron_007

If you want a PDF, check out the link to the blog format version, it has a link to a PDF. (Granted, the may have been created some time after the earlier comments were made) Personally, I prefer to copy the blog text into my personal "tips" collection so I can subsequently modify it and find it by searching in my tips collection document.

KeithAu001
KeithAu001

Or do you want them to do everything for you?

KeithAu001
KeithAu001

Why not create your own PDF, these guys do a lot to give us info, its up to you to figure out how to save it for future reference!

melodyjamowa
melodyjamowa

I agree. It would more useful to have a PDF of the lesson that I could share, rather than having to cut/paste everything. BTW... 'sharing' a link isn't always best as it often gets stripped or blocked by some ISPs. Good lesson!

laura.walker
laura.walker

Subtracting dates 1/3/2008 from 3/15/2008 (notice that the 2nd date has changed) will yield the same 72 days difference answer in all but: first equation (ans. was 2 months (implied)) and =YEAR(B4)*10000+MONTH(B4)*100+Day(B4) (ans. was 212 days).

jhecker
jhecker

It sounds like anchoring your references is more of a personal preference. If "Moving" cells is your concern, formulae automatically adjust. Regardless, I respect your opinion. As for the use of the Sum function that way goes, to the casual user the portion =Sum($D2;$B3 might not clearly indicate that 2 cells are being added. In my experience, only more experienced users are even aware of the multi range capabilities of such functions. I guess it is my preference, to expedite clear communication to keep things as clearas possible. Use of the Sum function to enclose calculations other than "Summing" a range has always seemed like someone doesn't know how to create a formula "correctly". One of those "pet peeves" I guess. Be well.

kirk.bare
kirk.bare

The formula as MonthYear works in this context. But in other formulas it could cause problems. So when I put months and years together I do the year *100 plus month. It's numeric and to me easier to work with. A few extra key strokes though. By Concatenating Month in front of Year, you do not get a very good sorting order. Also, Concatenating Year and Month although it can work, when sorted could cause problems unless you format the 'month' portion as always with two digits ('01' for January. Trying to create the value for the dates you are trying to summarize should also be created so they would have the same sorting order.

john
john

Multiplying year by 100 avoids possible conflicts. Say you were projecting a value out to August 2018 - the merged value from August 2010 would be the same rather than 201008 and 201808.

kirk.bare
kirk.bare

I'm confused. If the day is of consequence then why not sort by the date value, no need to do a mathmatical conversion. Unless the dates have time values... but then just take the integer of the date value

satiutni
satiutni

Use one column with date in usual format (US or Other) - create a hidden column with year*10000 + month*100 + day to create a reverse date order numeric similar to the number described in the previous post sort using the hidden column to create an apparently natural date sort if the day is of no consequence then the year*100 + month formula is sufficient

jody.burton
jody.burton

I thought it was a re-oriented version of the original formula, which used the ampersand to create a text result. wolfjh used an addition symbol, making it a straight number calculation. And I agree with Kirk Bare (below) that for true sorting, the month should be forced as a two-digit result.