| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum data if dates in a column are w/in a specific range
I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a
certain date range. Any tips/advice? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
Don't multi-post - refer to your other post for a solution.
Pete On Aug 13, 4:55*pm, Dhardy wrote: I am trying to have Excel sum data in column “M” if data in column “A” is a certain date range. Any tips/advice? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
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 --------------- Jacob Skaria "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
=SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000)
=sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
For example:
=SUMPRODUCT(--(A1:A100DATEVALUE("7/1/2009")),--(A1:A100<DATEVALUE("7/23/2009")),(H1:H100)) -- Gary''s Student - gsnu200860 "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
How do I tell it to calcuate the date range of 8/1/2009-8/7/2009?
Thanks in advance for your help! "Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
Please disregard previous question...sorry and thanks.
"Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
:-) No Problem!
"Dhardy" wrote: Please disregard previous question...sorry and thanks. "Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
What do I need to change in the formula that you provided if I am evaluating
data from another workbook? "Sean Timmons" wrote: :-) No Problem! "Dhardy" wrote: Please disregard previous question...sorry and thanks. "Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
=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" wrote: What do I need to change in the formula that you provided if I am evaluating data from another workbook? "Sean Timmons" wrote: :-) No Problem! "Dhardy" wrote: Please disregard previous question...sorry and thanks. "Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
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. "Sean Timmons" wrote: =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" wrote: What do I need to change in the formula that you provided if I am evaluating data from another workbook? "Sean Timmons" wrote: :-) No Problem! "Dhardy" wrote: Please disregard previous question...sorry and thanks. "Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
This formula worked
=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" wrote: =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" wrote: What do I need to change in the formula that you provided if I am evaluating data from another workbook? "Sean Timmons" wrote: :-) No Problem! "Dhardy" wrote: Please disregard previous question...sorry and thanks. "Sean Timmons" wrote: =SUMPRODUCT(--(A2:A1000=DATEVALUE("1/1/09"),--(A2:A1000<DATEVALUE("8/1/09"),M2:M1000) =sumif( "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Dhardy" wrote: I am trying to have Excel sum data in column €œM€ if data in column €œA€ is a certain date range. Any tips/advice? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum data if dates in a column are w/in a specific range
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 On Aug 13, 8:06*pm, lexmarcos wrote: 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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Dhardy" wrote: I am trying to have Excel sum data in column “M” if data in column “A” is a certain date range. Any tips/advice?- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting records in a column range occuring between specific dates | New Users to Excel | |||
| Multiplying specific data within a range to get column totals | Excel Worksheet Functions | |||
| Defining Range within Specific Dates | Excel Worksheet Functions | |||
| Using a range of dates to add data in a different column? | Excel Worksheet Functions | |||
| Using a range of dates to add data in a different column? | Excel Worksheet Functions | |||
Linear Mode
