Sum values between two dates
 

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

Sum values between two dates
 

On 20 Feb 2007 14:26:11 -0800, "Midget" wrote:

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