Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

How do I SUMIF two date ranges and filter with a value? The date range formula I've been using is:

=sumif($A:$A,"<="&D$5,$C:$C)-sumif($A:$A,"<"&D$4,$C:$C)  

Which works great for summing all values within column C. But as my sheet gets larger it would be nice to add a value filter, which in this example is (D6:) Food

Column A     Column B   Column C      Column D 
7/25/2012    Food       $9.26                                      
7/25/2012    Clothes    $40.00        D4 = Start Date (7/16/2012)
7/24/2012    Fast Food  $10.00        D5 = End Date   (7/20/2012)       
7/21/2012    Gas        $471.50    D6 = Value      (Food)       
7/18/2012    Food       $3.60          Sum of results = $516.49    
7/23/2012    Food       $478.73 
7/23/2012    Food       $34.16  
7/16/2012    Gas        $82.33  
7/15/2012    Food       $74.00 
7/15/2012    Rent       $400.00
share|improve this question
add comment

2 Answers 2

In order to reproduce the result, given by the OP, you need to use this formula:

=SUM(FILTER(C2:C,B2:B=D6,A2:A>=D4,A2:A<=D5))

Apart from that, you need to change the end date from 7/20/2012 to 7/23/2012

See example file I prepared: SUM with FILTER

share|improve this answer
add comment
up vote -1 down vote accepted

Here's the answer for reference:

=sum(filter(A:A,C2:C16=$D$6,A2:A>=D4,A2:A16<=D2))

share|improve this answer
    
You apply the string filter to Column C. That must be Column B. The first argument of the FILTER function is the range it needs to filter. In your case that must be Column C. –  Jacob Jan Tuinstra Apr 26 '13 at 9:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.