If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamically determining when a month ends
Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending in Jan., and only 10 in Feb. There has to be a way to do this. I've been playing around with it, and this is as far as I got... =SUMIF(Income!A2:A700,=AND("=38718","=38748")),I ncome!C2:C700) That would be for the month of Jan. (I have calc. the numbers from the number of days that 1/1/06 and 1/31/06 are from Jan. 1, 1990) I think my problem lies within my "AND" statement. Is the syntax correct? Is the semantics correct?? Is there an easier way to do this?? If my explaination was unclear in any way, just ask me to clairfy, and I'd be glad to try and do that. Thanks for anyones help in advance, I appreciate your time!! - Ian -- Moomancow3k ------------------------------------------------------------------------ Moomancow3k's Profile: http://www.excelforum.com/member.php...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
#2
|
|||
|
|||
Dynamically determining when a month ends
One way is to use SUMPRODUCT
In say, Sheet1: Put the Start date in A1: 01-Jan-06, End date in B1: 31-Jan-06 Then put in say, C1: =IF(OR(A1="",B1=""),"",SUMPRODUCT((Income!$A$2:$A$ 700=A1)*(Income!$A$2:$A$7 00=B1),Income!$C$2:$C$700)) C1 can be copied down to return correspondingly for other pairs of start/end dates in A2:B2, A3:B3, etc -- Max, Singapore GMT+8, xl97 Samples archive at: http://savefile.com/projects/236895 xdemechanik -- "Moomancow3k" wrote in message ... Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending in Jan., and only 10 in Feb. There has to be a way to do this. I've been playing around with it, and this is as far as I got... =SUMIF(Income!A2:A700,=AND("=38718","=38748")),I ncome!C2:C700) That would be for the month of Jan. (I have calc. the numbers from the number of days that 1/1/06 and 1/31/06 are from Jan. 1, 1990) I think my problem lies within my "AND" statement. Is the syntax correct? Is the semantics correct?? Is there an easier way to do this?? If my explaination was unclear in any way, just ask me to clairfy, and I'd be glad to try and do that. Thanks for anyones help in advance, I appreciate your time!! - Ian -- Moomancow3k ------------------------------------------------------------------------ Moomancow3k's Profile: http://www.excelforum.com/member.php...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
#3
|
|||
|
|||
Dynamically determining when a month ends
When I tried to enter your code into a new spreadsheet file, I put the data you said to into sheet1, as well as the line of code... I renamed sheet2 "Income", and entered some data... However, I get an "Invalid Cell Reference Error" on Sheet1 with your forumula... Any ideas? I copied and pasted your line for Sheet1!C1 exactly. Thanks! -- Moomancow3k ------------------------------------------------------------------------ Moomancow3k's Profile: http://www.excelforum.com/member.php...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
#4
|
|||
|
|||
Dynamically determining when a month ends
I'm not sure what went wrong for you,
but here's a sample implementation: http://cjoint.com/?blxSiW6QdK Moomancow3k_newusers.xls I've tried to structure it to follow closely the sheetname, range references, etc you provided earlier in your posted formula: =SUMIF(Income!A2:A700,=AND("=38718","=38748")),I ncome!C2:C700) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Moomancow3k" wrote in message ... When I tried to enter your code into a new spreadsheet file, I put the data you said to into sheet1, as well as the line of code... I renamed sheet2 "Income", and entered some data... However, I get an "Invalid Cell Reference Error" on Sheet1 with your forumula... Any ideas? I copied and pasted your line for Sheet1!C1 exactly. Thanks! -- Moomancow3k ------------------------------------------------------------------------ Moomancow3k's Profile: http://www.excelforum.com/member.php...o&userid=30339 View this thread: http://www.excelforum.com/showthread...hreadid=500025 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need cell formula to subtotal gross by month for a quarter | mikeburg | General Discussion | 4 | November 7th, 2005 09:25 PM |
4 and 5 week months | Big Rick | General Discussion | 15 | November 7th, 2005 12:32 AM |
Scheduling feature for others calendar ends at month end, why? | Luper910 | Database Design | 1 | October 26th, 2005 07:58 PM |
want other value in query and report | GI Smith | New Users | 10 | January 5th, 2005 02:03 AM |
How do I print the small calendars the month before and the month | mr_breeze_60 | Calendar | 1 | December 11th, 2004 09:29 AM |