# Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

## 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.

Thanks,
Tony

## Re: Running Totals formula?

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.

## Re: Running Totals formula?

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.

## Re: Running Totals formula?

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

## Re: Running Totals formula?

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.

## repost

Posted by IML on November 02, 2001 2:24 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.

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.

## Re: repost

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

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

## Re: repost

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

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

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

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

: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

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

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

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.

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

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

: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?

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

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

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.

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

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

: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!!

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

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

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

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

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

: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

## Re: repost

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

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.

## Re: repost

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

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

## Re: repost

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

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.

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

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

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

This archive is from the original message board at www.MrExcel.com.