Sum values between two dates

[Midget]

I have a multi worksheet issue here. I have data on one sheet and I
want to show reports on a second sheet. I am trying to check
ColumnA1:A1500 for dates that lie between 1/6/07 and 2/3/07, then sum
the cells in ColumnL1:L1500. I can get it to do it if i select
greater than or less than a certain day, but not both. I want to
gather data for a whole month(first friday to first friday). My
working formula is this...
=SUMIF(SHEET1!A5:A1500,">1/5/07",SHEET1!L5:L1500)
I am unable to actually select between two dates for some reason.
Any help would be appreciated. Sorry if there is an easy explanation,
I can't seem to put it together.
TIA

Ryan

 

[Ron Rosenfeld]

I have a multi worksheet issue here. I have data on one sheet and I
want to show reports on a second sheet. I am trying to check
ColumnA1:A1500 for dates that lie between 1/6/07 and 2/3/07, then sum
the cells in ColumnL1:L1500. I can get it to do it if i select
greater than or less than a certain day, but not both. I want to
gather data for a whole month(first friday to first friday). My
working formula is this...
=SUMIF(SHEET1!A5:A1500,">1/5/07",SHEET1!L5:L1500)
I am unable to actually select between two dates for some reason.
Any help would be appreciated. Sorry if there is an easy explanation,
I can't seem to put it together.
TIA

Ryan

Try something like this:

=SUMIF(SHEET1!A5:A1500,">1/5/07",SHEET1!L5:L1500)-
SUMIF(SHEET1!A5:A1500,">2/3/07",SHEET1!L5:L1500)

You may need to change the second date equality test.

It's a bit safer to either have the date in some cell and use the cell
reference in the formula; or use the DATE function DATE(yr,mo,day) to generate
the date. Especially if this might run on a machine that has it's Windows
regional settings different from yours. The syntax would be:

">"& cell_ref

or

">" & DATE(yr,mo,day)


--ron

 

[Midget]

Worked like a charm. Thanks for the input! I changed the formula to
look at a range of dates that I included on the report sheet. This
shows the range of dates, begin and end, in which the numbers that
follow are associated. Like this...

=SUMIF(PremierData!$A$5:$A$1500,">"&$B7-1,PremierData!L5:L1500)-
SUMIF(PremierData!$A$5:$A$1500,">"&$C7+1,PremierData!L5:L1500)

Since I actually wanted to include the begin and end days in the
equation, i added to the end, and subtracted from the begin.
Thanks again

Ryan