calculating between 2 given dates

[spudsnruf]

Hello

I would like to learn how to calculate amonts in a sumif that are between 2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
......where A8 is currently just a month reference (I mean it looks at cell A8
where I type in the month). So in other words, I'd like it to look up values
in another worksheet called Costs Detail, providing the associated dates are
between, say 1st Feb and end of April. Any help always please.

 

[T. Valko]

One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,">="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,">"&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542

 

[Jim Thomlinson]

I assume the equal sign here is an oversight...

)-=SUMIF(

 

[T. Valko]

Correction:

=SUMIF('Costs detail'!$Q$2:$Q518,">="&A8,'Costs
detail'!$O$2:$O$518)-SUMIF('Costs detail'!$Q$2:$Q518,">"&A9,'Costs
detail'!$O$2:$O$518)

Removed the equal sign before the 2nd SUMIF.

 

[spudsnruf]

Thanks Very much (and noted the comment about different sized ranges). Is
there actually a way of doing the dates within the formula something like
this..

SUMIF('Costs detail'!$Q$2:$Q518,Date>1,2,2008,Date<30,4,2008,'Costs
detail'!$O$2:$O$542)...ie so that within the formula I list the date before
and after that I want to apply?

Thanks

 

[T. Valko]

calculate amonts in a sumif that are between 2 dates.

In 99 out of 100 cases when someone says "between 2 dates" what they really
mean is greater than or equal to a start date and less than or equal to an
end date.

SUMIF('Costs detail'!$Q$2:$Q518,Date>1,2,2008,Date<30,4,2008,'Costs
detail'!$O$2:$O$542)...

The logic of the above would *exclude* the following dates (m/d/y format):

2/1/2008
4/30/2008


Try one of these:

=SUMIF('Costs detail'!$Q$2:$Q518,">="&DATE(2008,2,1),'Costs
detail'!$O$2:$O$518)-SUMIF('Costs
detail'!$Q$2:$Q518,">"&DATE(2008,4,30),'Costs
detail'!$O$2:$O$518)

=SUMPRODUCT(--('Costs detail'!$Q$2:$Q518>=DATE(2008,2,1)),--('Costs
detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518)

 

[T. Valko]

You're welcome. Thanks for the feedback!