SUMIF Based on a date range

[pstewart]

Formula will be in O11. I need a sumif formula that will look at the dates in Column B and will only sum the cells in column I that fall within the range 1 Aug - 7 aug. Any assistance is appreciated.

[vlady]

maybe also using sumifs

Please Login or Register  to view this content.

you can change the date as reference

say X1 = beginning date
X2 = ending date

=SUMIFS(I6:I64,B6:B64,">="&X1,B6:B64,"<="&X2,Y6:Y64,"=true")

hth.

[Max, Singapore]

Another way, in O11:
=SUMPRODUCT(($B$6:$B$36>=--"01 Aug 2012")*($B$6:$B$36<=--"07 Aug 2012"),$I$6:$I$36)

[Ace_XL]

=SUMIFS(I6:I64,B6:B64,">="&DATEVALUE("8/1/2012"),B6:B64,"<="&DATEVALUE("8/7/2012"),Y6:Y64,"=true")

vlady's formula works. However you could replace the "=true" with just TRUE and it will still work!

[Pepe Le Mokko]

Another way, in O11:
=SUMPRODUCT(($B$6:$B$36>=--"01 Aug 2012")*($B$6:$B$36<=--"07 Aug 2012"),$I$6:$I$36)

I don't see what the unary operator is for in this case ?

[Max, Singapore]

The "--" converts the text dates into real dates. I like to use this kind of text dates, eg: "01 Aug 2012" as it is unambiguous, internationally readers know exactly which date it is, regardless of regional date formats/settings