Dynamically determining when a month ends

[Moomancow3k]

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")),Income!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

[Max]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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")),Income!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
>

[Moomancow3k]

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!

[Max]

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")),Income!C2:C700)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moomancow3k" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>