Formula for Running total

C

Crystal

Ok I am a Dispatcher for a local Police Agency and my Boss has given me a
task. We have to keep a daily running total of all calls and type of call.
What I need help on is finding the formula that would do the following. In
Column A I have the Type of Calls, In Column B I have the Present Day's Total
Calls, and in Column C I need the running YTD Total. At first I thought the
formula would be =YTD+B2 but it turned out that it does not keep a RUNNING
total. PLEASE SOMEONE HELP ME!
 
J

Jorge Rodrigues

Ok, Crystal, never mind. Perhaps is difficult to get the file... Follows the
hint, I hope you understand:
Col B ColC
40 In C1 put: =B1 (Result: 40)
5 In C2 put: =IF(B2="";"";SUM($B$1:B2)) (Result: 45)
20 In C3 paste the formula below (Result:65)
C4,C5 ..... with the same formula.
So, when you put in ColB a number, in ColC must appear the YTD total.

Regards.
Jorge
 
C

Crystal

Hello again... what I am looking for is the Formula that when you add a
Number to (B) on a daily basis that (C) would keep a running YTD total. Can
someone Please help me?
(A) (B) (C)
TODAY 2004
PHONE CALLS 24,329
CELL CALLS 9,610
911 CALLS 2,607
877 CALLS 506
DUI CALLS 590
PEOPLE/POST 1,200
CAR CBA 174
POST CBA 10
MVI CHECKS 117
FOOT PATROLS 397
SCHOOL BUS FOLLOWED 30
SCHOOL BUS HP-7'S 0
SCHOOL ZONES WORKED 3
SCHOOL ZONE HP-7'S 0
 
J

Jorge Rodrigues

Another approach:
Assuming that your daily data is from B1 to B30, so the total can appear
only in C1, something like =IF(B1="";0;SUM($B$1:B30))
(I don't know if in your country the separator is ; or ,)
Regards
Jorge
 
T

Tom Ogilvy

At the end of each day, you need to move your daily totals to a master data
sheet so you have an audit trail. Then in this sheet you could sum up that
data using Sumif

Basically formulas don't store information and even though this can be
simulated with intentional circular references, I don't think it would be
very reliable. If you made an entry mistake, you could have trouble
reconstructing your data.
 
J

Jorge Rodrigues

I agree with Tom, but if you want really something like you wrote, try VB,
something like this:

Private Sub CommandButton1_Click()
Dim myRange As Range
Dim Var, Var1 As Long
Set myRange = Worksheets("Sheet1").Range("B1:B10")
Var = Range("C1").Value
Var1 = Application.WorksheetFunction.Sum(myRange) + Var
Var = Var1 - Var
Range("C1") = Var

End Sub

So, if you change the number of one of the cells B, in C1 you have the
running Total.
Regards.
Jorge
 
Top