How format SUMIF formula for matching date range and column value?

  • Thread starter Thread starter Kane
  • Start date Start date
K

Kane

Here is my current SUMIF formula. How would I modify this to add the
selection of a date range from the same data columns? Example, start date of
2/1 and end date of 2/8.

=SUMIF(Jan!$F$9:$F$55202,"groceries",Jan!$D$9:$D$55202)
 
The answer depends on which version of Excel you are using. For Excel 2007,
you can use Sumifs. For other versions, it's Sumproduct, as in:

=Sumproduct(--(Jan!$F$9:$F$55202="groceries"),--(Jan!$G$9:$G$55202>=Date(2010,2,1)),--(Jan!$G$9:$G$55202<=Date(2010,2,8)),Jan!$D$9:$D$55202)

As an aside, my bet is that using a Pivot Table would make your life a lot
easier.

Regards,
Fred
 
=SUMPRODUCT(--(RngF="groceries"),--(RngDate>=DATE(2010,2,1)),--(RngDate<=DATE(2010,2,8)),RngD)
 
I am using Office 2007. So the sumif is working great. I tried the pivot
tables first, but they were very complex to setup and I could not understand
or change the format of the results. Is there a tutorial anywhere that can
help?
 
Back
Top