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

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)
 
F

Fred Smith

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
 
T

Teethless mama

=SUMPRODUCT(--(RngF="groceries"),--(RngDate>=DATE(2010,2,1)),--(RngDate<=DATE(2010,2,8)),RngD)
 
K

Kane

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify SUMIF to find data range in addition to value 4
SUMIF date criteria 3
Multiple Columns for SUMIF formula?!?! 2
SumIf conditional to date range 5
What should formula look like to define date range on SUMIF 2
How can I define date range criteria in SUMIF formula? 1
Sumif across a table 1
How to Control a formula by a character in adjacent column on same 4

Top