Archive of Mr Excel Message Board

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

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.

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

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

: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

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.

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

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.

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

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

: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

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.

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

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.

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