MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Running Totals formula?


Posted by Tony on November 02, 2001 11:15 AM

I would like to keep running totals for the amount of money i spend for each item i purchase each month(eg:WD Hard Drives=$150 X 17 purchases = XXX). AT the end of each month i'd like to be able to look at one cell and know the total amount of money i spent each item.

Please advise formula.

Thanks,
Tony


Posted by Todd on November 02, 2001 11:26 AM

Like if the elements are in b column and running total in c column, do something like =b5+c4.

Posted by Tony on November 02, 2001 11:50 AM

:That formula didn't seem to work. Are you sure it's complete? It looks like it's missing something.


Posted by IML on November 02, 2001 12:01 PM

Lets say you have your dates in column A, Your description in column B, and your amount in column C. Your data is in rows 2 through 100.
In cell G1, you type in the date of interest (ie 10/2001 for october). starting in F2, list your unique entries ie (WD hard drive) in F2, WD2 hard drive in F3, etc. You may want to use advanced filter to achieve this.
You can get a total by entering this formula in G2, and copying it down
=SUM(($A$2:$A$100>DATE(YEAR($G$1),MONTH($G$1),0))*($A$2:$A$100<DATE(YEAR($G$1),MONTH($G$1)+1,1))*($B$2:$B$100=F2)*($C$2:$C$100))

Be sure to hit control shift enter for this array formula to work. If this sound off base, please provide details of how your table is set up.

good luck

Posted by Todd on November 02, 2001 12:04 PM

Lets say you have data in b1:b20. in c1, put =b1
in c2 put =b2+c1
fill down from c2 through c20.


Posted by IML on November 02, 2001 2:24 PM

repost

Lets say you have your dates in column A, Your description in column B, and your amount in column C. Your data is in rows 2 through 100.

sorry, that formula could be
=SUM(($A$2:$A$100 > DATE(YEAR($G$1),MONTH($G$1),0))*($A$2:$A$100 < DATE(YEAR($G$1),MONTH($G$1)+1,1))*($B$2:$B$100=F3)*($C$2:$C$100))

again, remember to hit enter when control and shift are depresses.

Posted by Tony on November 05, 2001 4:09 AM

Re: repost

:At what point do i hit Ctrl,Shft,Enter?? After i've enetered the entire formula? During? Before?

Posted by . on November 05, 2001 4:22 AM

Re: repost


...just try it !! Do what IML said - enter the formula by pressing Ctrl+Shift+Enter.

Posted by Tony on November 05, 2001 5:50 AM

Re: repost / I almost have it but,,still not working


:I entered all columns with data (dates,desc,etc) as you directed IML. I enetered the entire formula as you specified and a "0" appeared in G2 as if the program is waiting for something. What do i need to do to make this formula work?? Thx

Posted by IML on November 05, 2001 6:14 AM

Re: repost / I almost have it but,,still not working

If the formula is already in the cell, click on it. Next hit F2 to get into edit mode. No just hit enter while control and shift are depressed. If you do it right, braces {} will appear around the formula in the formula bar. Once you've done this in the first cell, you can just copy it down do the rest of your list with no special entry required.

Posted by Tony on November 05, 2001 7:07 AM

Re: repost / I almost have it but,,still not working


:OK, i did it. The braces appeared in the formula bar as you said. However, G2 still has a "0" in it's cell. It did not pull my amount data over. What am i doing wrong?

Posted by IML on November 05, 2001 7:26 AM

Re: repost / I almost have it but,,still not working

Good question. If we eliminate that the correct answer is zero, the likely cuprits are:
The name you put is your unique list does not exactly match what was entered in your list (test this by copying the description from your list and pasting it over what is typed in your unique list).
double check the date entered in G1 is truly a date (like 10/1/01) etc.

If not, try posting the first few lines of your data and we can try to rework it.


Posted by Tony on November 05, 2001 10:43 AM

Re: repost / I almost have it but,,still not working

:Thanks for all your help IML. Unfortunaely, i can't seem to get the formula to work properly. I think i'll just keep the running totals manually. Thanks again!!


Posted by IML on November 05, 2001 11:04 AM

Re: repost / I almost have it but,,still not working

That's fine. My last offer would be if you would just post say three lines of your data. An easy way to do this is as follows.
Lets say your first three rows are in A1:E4.
Type =A1:e4 in a blank cell and hit enter. (ignore error)
Highlight =A1:e4 in the formula bar and hit f9. Now hit control C to copy. Just paste it into here regardless of what it look likes. There is probably a simple solution. If not, that fine too.

good luck
Click

Posted by Tony on November 06, 2001 5:42 AM

Re: repost / I almost have it but,,still not working

:OK, here is the data. I would like to keep running totals for the amount of money i spent for each item in my purchasing spreadsheet (IE:If i purchased memory 5 times within the month of November at $50.00 each, i would like a cell to show the total November purchase amount of $250.00 for that item (and so-on for my other items). It would be easier than scanning my spreadsheet and picking out each item respectively.

Date Qty Desc Mfg Cost
11/1/2001 1 WD HDD1 comp $150.00
11/5/2001 1 Memory Kingston $180.00
11/6/2001 1 Monitor Quantum $200.00


Posted by iml on November 06, 2001 7:21 AM

Re: repost

Okay, your table is in column A-E
In G1, put the date in question, ie 11/01/01 for November
Starting in F2, list your unique name (Wd Hdd1 in F2, Memory in F3, Monitor in F4) etc. This can be done by advance filter for a long list.
Now in cell G2, array enter (control shift enter) this formula
=SUM(($A$2:$A$100 > DATE(YEAR($G$1),MONTH($G$1),0))*($A$2:$A$100 < DATE(YEAR($G$1),MONTH($G$1)+1,1))*($C$2:$C$100=F2)*($B$2:$B$100))
this should give you the sum of your quanities
in H2, array enter
=SUM(($A$2:$A$100 > DATE(YEAR($G$1),MONTH($G$1),0))*($A$2:$A$100 < DATE(YEAR($G$1),MONTH($G$1)+1,1))*($C$2:$C$100=F2)*($E$2:$E$100))
This should give the sum of the costs.

Now copy G2 and H2 as far down as your unique list goes.

Posted by Tony on November 06, 2001 11:27 AM

Re: repost

:Hey IML, the qty sums worked great!! Can't seem to get the cost sums to work. Any ideas?

Posted by IML on November 06, 2001 11:45 AM

Re: repost

Two ideas:
1) Make sure the braces are around both formulas to make sure it is array entered. If not, hit f2 and control shift enter

If they both look correct, I'd just copy the qty sum formula over to the cell to the right of it and edit it as follows:
Change G2 to F2. Change ($B$2:$B$100) to ($e$2:$e$100) and again hit control shift enter.

Posted by Tony on November 06, 2001 12:05 PM

YES,, IT WORKED!!!!!! Thanks IML!! You 'da Man!!

:Thanks for not giving up on me IML. This will make my job incredibly easier. Thanks!