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

 

[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)

"Moomancow3k" <[email protected]>
wrote in message