D
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?
certain date range. Any tips/advice?
P
Pete_UK
Don't multi-post - refer to your other post for a solution.
Pete
Pete
J
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
=SUMPRODUCT(--(A1:A100>=C1),--(A1:A100<=D1),M1:M100)
If this post helps click Yes
S
Sean Timmons
=SUMPRODUCT(--(A2:A1000>=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000)
=sumif(
=sumif(
G
Gary''s Student
For example:
=SUMPRODUCT(--(A1:A100>DATEVALUE("7/1/2009")),--(A1:A100<DATEVALUE("7/23/2009")),(H1:H100))
=SUMPRODUCT(--(A1:A100>DATEVALUE("7/1/2009")),--(A1:A100<DATEVALUE("7/23/2009")),(H1:H100))
D
Dhardy
How do I tell it to calcuate the date range of 8/1/2009-8/7/2009?
Thanks in advance for your help!
Thanks in advance for your help!
D
Dhardy
Please disregard previous question...sorry and thanks.
S
Sean Timmons
No Problem!Dhardy said:
D
Dhardy
What do I need to change in the formula that you provided if I am evaluating
data from another workbook?
data from another workbook?
S
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...
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...
D
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(--([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.
Sean Timmons said:
D
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("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!
Sean Timmons said:
L
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.
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.
P
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
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