Date and Month update

[guitarist_wo]

Ok, I have a finances spreadsheet that I want to put all of my expenses during a given month. I want to be able to automatically update a cell with the month depending on the date I enter in to a different cell. For example, I enter a date in a cell (9/20/06). To the right of that under my "Bills" column, I would enter in $50. Then, the next row, 5 days later I enter this date (9/25/06) with a payment of $30 in the column to the right. Then, my next payment is next month (10/5/06) of $70.

I want to update a different part of my spreadsheet automaticaly that has the total montly payments. So, for that part of the spreadsheet under the September row for "Bills", I would have a total of $80. For the October row, I would have $70. In words, I want the formula to say this: "If this date in this cell equals the month of 9, then I want this cell, which is to the right of my date that holds the amount, to update in the September row under my Bills column". I'm not quite sure how to do that and whether it has to be a macro or just a formula. Its hard to explain exactly what I want, but I hope my description helps. Any help would be greatly appreciated. THANKS!

 

[guitarist_wo]

alright, here is a better explanation:

I want to enter a date into A16 and then an amount into B16 and have it update automatically into B3. So, as I have it in the picture, I want to add up all of the amounts for October into B3, all of the amounts paid in November into B4, etc. I need something that can read the date in column A row 16-26, once it is entered, and be able to transfer that to the name of the month above (i.e. 10/20/06 and 10/25/06 point to the month of October). Then add up everything from the same months from columns B, C, and D, to the corresponding month row above. I'm still not sure if this is possible with excel, but would like to try it out if I can.

 

[macajm]

In the Bills section, add the following formula in column c16 (& copy down):-
c16=year(a16)*100+month(a16)
this will produce numbers like 200611, 200611 etc.
Then in the Finances section enter the months as say first day of month and custom format as "Mmmm-yy".
repeat the formula from c16 in c3:c10.
Then enter the following formula in cell b3 and copy down to b10 (note $ refs):-
b3sumif(c$16:c$26,c3b$16:b$26)

 

[guitarist_wo]

ok, that works GREAT! but i found a problem. if i want to enter, say, 2 payments on separate rows (A16 and A17) for the month of september, then the formulas do not update the finances section correctly. it looks like it takes one row from the bills section, for every one row of the finances section.

thanks for the help.

 

[guitarist_wo]

in further response...taking the sum of B16 and B17 and throwing it to B3. Then taking the sum of B18, B19, and whatever other payments are in November, and throwing the sums of those cells to B4. That is the general idea of how I would like it to work.

 

[macajm]

I note in your example spreadsheet that one of your dates is 2005 not 2006 so this may have something to do with the fact that it is not accumulating into the listed month.
Also note that I assume you realised that I left out the = in the line
b3=sumif(c$16:c$26,c3b$16:b$26)

 

[macajm]

I note in your example spreadsheet that one of your dates is 2005 not 2006 so this may have something to do with the fact that it is not accumulating into the listed month.
Also note that I assume you realised that I left out the = in the line
b3=sumif(c$16:c$26,c3,b$16:b$26)

 

[guitarist_wo]

yeah, i got the "=" in there. the 2005 in there did not matter either. i changed it to 2006 and it would still not total the monthly payments into one cell in the "finances" sections. it put each payment across multiple cells in the "finances" section. i would like to get all of the october payments into cell B3 (meaning: sum up B16, B17, and whatever other payments occur in october to go into B3), all of the november payments into B4, and so on, no matter how many payments i have per month, whether it be 30 or just 1. is that possible?

 

[guitarist_wo]

this is what i have so far and it is updating wrong in the "Finances" section, column B.

I need B16 and B17 to sum up into B3. And B18:B21 needs to sum up into B4. And so on for the rest of the months.

 

[macajm]

looks like you have a problem with the formulae in c4 to c10
c3 seems to produce the right result, so if you have
c3=YEAR(A3)*100+MONTH(A3), you should be able to copy that formula down into c4 to c10
If you still have problems send me your email & I will send you my test spreadsheet.