Sumifs with dates

[cthompson]

Hi All

I am trying to us the sumifs function to total data between two dates. Below I have a begin week and week end date all I am attempting to do is sum an amount from a string of data where the transaction date is equal or greater than the begin week but less than or equal to the week end date.

Below is the formula with the between condition but excel will not accept the *OR condition
=SUMIFS(Sheet2!AH:AH,Sheet2!C:C,">="&C2 *or"<="&D2)


If I remove the conditional *OR it works in either the greater than and less than but not with a conditional between
=SUMIFS(Sheet2!AH:AH,Sheet2!C:C,">="&C2)
This works correctly but I want to sum everything between the dates

-- corrupted image removed --

Thanks again for the help
Chris

 

[sheetspread]

You can add single sumif() formulas instead or write a sumproduct.

 

[Weazel]

maybe something like...

=SUMIFS(Sheet2!AH:AH,Sheet2!C:C,">="&C2,Sheet2!C:C,"<="&D2)

 

[Scott T]

Try

=SUMPRODUCT(((Sheet2!C:C>=Sheet1!C2)+(Sheet2!C:C<=Sheet1!D2)-1),Sheet2!AH:AH)

 

[cthompson]

Perfect thank you both so much. Funny I had never used sumproduct before, learn something new everyday.
CT

 

[Weazel]

i'm not sure why you'd want to use sumproduct with full column references