Using SUMIFS with date range

  • Follow


I am trying to sum a column of cells if several criteria are met...1. equal 
to specific customer name, 2. is greater than or equal to a beginning date 
entered into a specific cell and 3. is less than or equal to an end date 
entered into another specific cell.   I can get the formula to work if I use 
the beginning and end dates in the SUMIFS formula, but I don't want to change 
the formula each time I run the spread.  So I want to be able to data enter 
the begin and end dates in certain cells (line in B1 and B2) so I only change 
those dates to recalculate the totals.
0
Reply Utf 1/26/2010 3:45:01 PM

Something like this:

=sumifs(A:A,B:B,B3,C:C,">="&B1,D:D,"<="&B2)

Regards,
Fred

"Catrina" <Catrina@discussions.microsoft.com> wrote in message 
news:D870BB3F-9698-4450-821F-5F5C26596FEF@microsoft.com...
>I am trying to sum a column of cells if several criteria are met...1. equal
> to specific customer name, 2. is greater than or equal to a beginning date
> entered into a specific cell and 3. is less than or equal to an end date
> entered into another specific cell.   I can get the formula to work if I 
> use
> the beginning and end dates in the SUMIFS formula, but I don't want to 
> change
> the formula each time I run the spread.  So I want to be able to data 
> enter
> the begin and end dates in certain cells (line in B1 and B2) so I only 
> change
> those dates to recalculate the totals. 

0
Reply Fred 1/26/2010 4:15:10 PM

Hi
I assume you enter the customer name in A1, start day in B1 and finish day 
in B2. then your information starts with customers name in cell A5, dates in 
B5 and you want to summarize column C

=sumproduct(--($A$5:$A$1000=$A$1),--($B$5:$B$1000>=$B$1),--($B$5:$B$1000<=$B$2),$C$5:$C$1000)

"Catrina" wrote:

> I am trying to sum a column of cells if several criteria are met...1. equal 
> to specific customer name, 2. is greater than or equal to a beginning date 
> entered into a specific cell and 3. is less than or equal to an end date 
> entered into another specific cell.   I can get the formula to work if I use 
> the beginning and end dates in the SUMIFS formula, but I don't want to change 
> the formula each time I run the spread.  So I want to be able to data enter 
> the begin and end dates in certain cells (line in B1 and B2) so I only change 
> those dates to recalculate the totals.
0
Reply Utf 1/26/2010 4:26:04 PM

Perfect.  Thank you!

"Fred Smith" wrote:

> Something like this:
> 
> =sumifs(A:A,B:B,B3,C:C,">="&B1,D:D,"<="&B2)
> 
> Regards,
> Fred
> 
> "Catrina" <Catrina@discussions.microsoft.com> wrote in message 
> news:D870BB3F-9698-4450-821F-5F5C26596FEF@microsoft.com...
> >I am trying to sum a column of cells if several criteria are met...1. equal
> > to specific customer name, 2. is greater than or equal to a beginning date
> > entered into a specific cell and 3. is less than or equal to an end date
> > entered into another specific cell.   I can get the formula to work if I 
> > use
> > the beginning and end dates in the SUMIFS formula, but I don't want to 
> > change
> > the formula each time I run the spread.  So I want to be able to data 
> > enter
> > the begin and end dates in certain cells (line in B1 and B2) so I only 
> > change
> > those dates to recalculate the totals. 
> 
> .
> 
0
Reply Utf 1/26/2010 8:24:01 PM

You're welcome. Thanks for the feedback.

Fred

"Catrina" <Catrina@discussions.microsoft.com> wrote in message 
news:1BCC67FA-1829-4CC1-B863-E065B763CD22@microsoft.com...
> Perfect.  Thank you!
>
> "Fred Smith" wrote:
>
>> Something like this:
>>
>> =sumifs(A:A,B:B,B3,C:C,">="&B1,D:D,"<="&B2)
>>
>> Regards,
>> Fred
>>
>> "Catrina" <Catrina@discussions.microsoft.com> wrote in message
>> news:D870BB3F-9698-4450-821F-5F5C26596FEF@microsoft.com...
>> >I am trying to sum a column of cells if several criteria are met...1. 
>> >equal
>> > to specific customer name, 2. is greater than or equal to a beginning 
>> > date
>> > entered into a specific cell and 3. is less than or equal to an end 
>> > date
>> > entered into another specific cell.   I can get the formula to work if 
>> > I
>> > use
>> > the beginning and end dates in the SUMIFS formula, but I don't want to
>> > change
>> > the formula each time I run the spread.  So I want to be able to data
>> > enter
>> > the begin and end dates in certain cells (line in B1 and B2) so I only
>> > change
>> > those dates to recalculate the totals.
>>
>> .
>> 

0
Reply Fred 1/27/2010 5:10:35 PM

4 Replies
1053 Views

(page loaded in 2.623 seconds)


Reply: