Calculate a SUMIF if criteria is between 2 date ranges

[Guest]

I want to create a SUMIF function but I only want it to return the value of
items within and inclusive of 2 dates. e.g the sum of column C if column A
has dates between 01/07/06 and 30/09/06 inclusive.

Similarly I want to create a COUNTA function for the same criteria.

I am using Microsoft Excel 2003.

 

[Carim]

Hi Anthony,

=SUMPRODUCT(($A$2:$A$20>"01/07/06")*($A$2:$A$20<="30/09/06"),$C$2:$C$20)

should do the job ... adjust the ranges to your needs


Carim

 

[Pete_UK]

You would normally do a sumif if less than 30/09/06 and subtract from
this a sumif if less than or equal to 01/07/06, so it would look
something like:

=SUMIF(A1:A1000,"<30/09/06",C1:C1000) -
SUMIF(A1:A1000,"<=01/07/06",C1:C1000)

Adjust the ranges to suit.

Hope this helps.

Pete

 

[Guest]

Many thanks for that Carim, unfortunately it still won't work as it keeps
telling me there is an error in the formula. Oh well back to the drawing
board.
Regards
Tony

 

[Carim]

Anthony,

Formula would work if you would extract the dates in separate cells and
use their addresses in the formula :

=SUMPRODUCT(($A$2:$A$20>D2)*($A$2:$A$20<=E2),$C$2:$C$20)

if cell D2 = 01/07/06 and cell E2 = 30/09/06

HTH
Carim