Sum data if dates in a column are w/in a specific range

[Dhardy]

I am trying to have Excel sum data in column “M†if data in column “A†is a
certain date range. Any tips/advice?

 

[Pete_UK]

Don't multi-post - refer to your other post for a solution.

Pete

 

[Jacob Skaria]

With the start and end dates in C1 and D1 ;try the below formula

=SUMPRODUCT(--(A1:A100>=C1),--(A1:A100<=D1),M1:M100)

If this post helps click Yes

 

[Sean Timmons]

=SUMPRODUCT(--(A2:A1000>=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000)


=sumif(

 

[Gary''s Student]

For example:

=SUMPRODUCT(--(A1:A100>DATEVALUE("7/1/2009")),--(A1:A100<DATEVALUE("7/23/2009")),(H1:H100))

 

[Dhardy]

How do I tell it to calcuate the date range of 8/1/2009-8/7/2009?

Thanks in advance for your help!

 

[Dhardy]

Please disregard previous question...sorry and thanks.

 

[Sean Timmons]

No Problem!

Please disregard previous question...sorry and thanks.

 

[Dhardy]

What do I need to change in the formula that you provided if I am evaluating
data from another workbook?

 

[Sean Timmons]

=SUMPRODUCT(--([Book1]Sheet1!A2:A1000>=DATEVALUE("1/1/09"),--([Book1]Sheet1!A2:A1000<DATEVALUE("8/1/09"),[Book1]Sheet1!M2:M1000)

Of course, change Book1 and sheet1 to your workbook and sheet names.

It's actualyl easiest if you enter sumproduct( then click the Fx in yoru
address bar. Then, you can just cick and drag your sets desired and the
workbook and sheet will automatically show...

 

[Dhardy]

This is the formual I am working with:

SUMPRODUCT(--([Desiree.xls]August!$A$5:$A$500>=DATEVALUE("8/3/2009"),--([Desiree.xls]August!$A$5:$A$500<=8/7/2009,[Desiree.xls]August!$M$5:$M$500)

It is coming back with an error & I'm not sure why.

=SUMPRODUCT(--([Book1]Sheet1!A2:A1000>=DATEVALUE("1/1/09"),--([Book1]Sheet1!A2:A1000<DATEVALUE("8/1/09"),[Book1]Sheet1!M2:M1000)

Of course, change Book1 and sheet1 to your workbook and sheet names.

It's actualyl easiest if you enter sumproduct( then click the Fx in yoru
address bar. Then, you can just cick and drag your sets desired and the
workbook and sheet will automatically show...

What do I need to change in the formula that you provided if I am evaluating
data from another workbook?

 

[Dhardy]

This formula worke
=SUMPRODUCT(--([Book1]Sheet1!A2:A1000>=DATEVALUE("8/3/2009")),--([Book1]Sheet1!A2:A1000<=DATEVALUE("8/7/2009")),([Book1]Sheet1!M2:M1000))

Thank you SO much! You will be GOLD in no time!

=SUMPRODUCT(--([Book1]Sheet1!A2:A1000>=DATEVALUE("1/1/09"),--([Book1]Sheet1!A2:A1000<DATEVALUE("8/1/09"),[Book1]Sheet1!M2:M1000)

Of course, change Book1 and sheet1 to your workbook and sheet names.

It's actualyl easiest if you enter sumproduct( then click the Fx in yoru
address bar. Then, you can just cick and drag your sets desired and the
workbook and sheet will automatically show...

What do I need to change in the formula that you provided if I am evaluating
data from another workbook?

 

[lexmarcos]

I want to sum B1, with date (constant e.g. DATEVALUE ("1/1/09"), no start
and end) IN A1. Any advice is appreciated.

AB
1 1/1/09 10
2 1/2/09 20
3 1/1/09 10
4 1/3/09 15
5

If i can find a way to sum and return values in A5 to equal 20 (1/1/09 is
10 + 10)

Thanks.

 

[Pete_UK]

I suggest you use C1 to put the date you are interested in, and then
in D1 you can have ths formula:

=SUMIF(A:A,C1,B:B)

This way your data can be as long as you like in columns A and B.

Hope this helps.

Pete