| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum value between two dates and copy to new cell
Hi Guys,
Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, A B C D E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum value between two dates and copy to new cell
=SUMIF(C:C,"="&A2,B:B)SUMIF(D:D,""&A2,B:B)
-- HTH Bob "dave" wrote in message ... Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, A B C D E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum value between two dates and copy to new cell
On Mar 7, 3:36*pm, "Bob Phillips" wrote:
=SUMIF(C:C,"="&A2,B:B)SUMIF(D:D,""&A2,B:B) -- HTH Bob "dave" wrote in message ... Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, * * * A * * * * * * * * *B * * * C D * * * * * * * *E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave- Hide quoted text - - Show quoted text - hI Guys, Im getting an error value. I guess i've provided wrong explain and its confusing. Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to the date. C1 is 01/01/10 and D1 is 28/02/10. so all the value between these dates should be sum and display in column E. A B C D E 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thank you Dave |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum value between two dates and copy to new cell
Hi Dave
Bob inadvertently omitted the - sign between his 2 Sumifs =SUMIF(C:C,"="&A2,B:B)-SUMIF(D:D,""&A2,B:B) equally, Joel's formula should work fine. You might need to extend the ranges. If you are still getting error's, then there must be something wrong with your data. Are they true Excel dates, or Text dates? -- Regards Roger Govier dave wrote: On Mar 7, 3:36 pm, "Bob Phillips" wrote: =SUMIF(C:C,"="&A2,B:B)SUMIF(D:D,""&A2,B:B) -- HTH Bob "dave" wrote in message ... Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, A B C D E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave- Hide quoted text - - Show quoted text - hI Guys, Im getting an error value. I guess i've provided wrong explain and its confusing. Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to the date. C1 is 01/01/10 and D1 is 28/02/10. so all the value between these dates should be sum and display in column E. A B C D E 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thank you Dave |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum value between two dates and copy to new cell
On Mar 7, 6:00*pm, Roger Govier
wrote: Hi Dave Bob inadvertently omitted the - sign between his 2 Sumifs =SUMIF(C:C,"="&A2,B:B)-SUMIF(D:D,""&A2,B:B) equally, Joel's formula should work fine. You might need to extend the ranges. If you are still getting error's, then there must be something wrong with your data. Are they true Excel dates, or Text dates? -- Regards Roger Govier dave wrote: On Mar 7, 3:36 pm, "Bob Phillips" wrote: =SUMIF(C:C,"="&A2,B:B)SUMIF(D:D,""&A2,B:B) -- HTH Bob "dave" wrote in message .... Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, * * * A * * * * * * * * *B * * * C D * * * * * * * *E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave- Hide quoted text - - Show quoted text - hI Guys, Im getting an error value. I guess i've provided wrong explain and its confusing. Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to the date. C1 is 01/01/10 and D1 is 28/02/10. so all the value between these dates should be sum and display in column E. * * * * A * * * * * *B * * * * * * * C D * * * * * * * * * *E 04/01/2010 * * *12 * * *01/01/2010 * * *28/02/2010 02/04/2010 * * *133 * * 01/03/2010 * * *30/04/2010 05/06/2010 * * *154 * * 01/05/2010 * * *30/06/2010 03/04/2010 * * *112 * * 01/07/2010 * * *31/08/2010 05/05/2010 * * *65 * * *01/09/2010 * * *31/10/2010 06/07/2010 * * *34 * * *01/11/2010 * * *31/12/2010 02/01/2010 * * *26 02/03/2010 * * *25 14/02/2010 * * *85 17/08/2010 * * *99 Thank you Dave- Hide quoted text - - Show quoted text - Hi Roger, Below is what is the output i received. A B C D E 04/01/2010 12 01/01/2010 28/02/2010 -12 02/04/2010 133 01/03/2010 30/04/2010 -133 05/06/2010 154 01/05/2010 30/06/2010 -154 03/04/2010 112 01/07/2010 31/08/2010 -133 05/05/2010 65 01/09/2010 31/10/2010 -154 06/07/2010 34 01/11/2010 31/12/2010 -112 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Im getting -12. Its should be 12 + 26 +85 = 123. all these values are between 01/01/10 and 28/02/10. any solution? cheers dave |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum value between two dates and copy to new cell
Hi Dave
I'm sorry, I just looked at the logic of the formulae, without looking at your data Change Bob's formula to =SUMIF(A:A,""&C2,B:B)-SUMIF(A:A,"="&D2,B:B) or change Joel's formula to =SUMPRODUCT(--($A$2:$A$11=$C2),--($A$2:$A$11<=$D2),B$2:B$11) -- Regards Roger Govier dave wrote: On Mar 7, 6:00 pm, Roger Govier wrote: Hi Dave Bob inadvertently omitted the - sign between his 2 Sumifs =SUMIF(C:C,"="&A2,B:B)-SUMIF(D:D,""&A2,B:B) equally, Joel's formula should work fine. You might need to extend the ranges. If you are still getting error's, then there must be something wrong with your data. Are they true Excel dates, or Text dates? -- Regards Roger Govier dave wrote: On Mar 7, 3:36 pm, "Bob Phillips" wrote: =SUMIF(C:C,"="&A2,B:B)SUMIF(D:D,""&A2,B:B) -- HTH Bob "dave" wrote in message ... Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, A B C D E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave- Hide quoted text - - Show quoted text - hI Guys, Im getting an error value. I guess i've provided wrong explain and its confusing. Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to the date. C1 is 01/01/10 and D1 is 28/02/10. so all the value between these dates should be sum and display in column E. A B C D E 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thank you Dave- Hide quoted text - - Show quoted text - Hi Roger, Below is what is the output i received. A B C D E 04/01/2010 12 01/01/2010 28/02/2010 -12 02/04/2010 133 01/03/2010 30/04/2010 -133 05/06/2010 154 01/05/2010 30/06/2010 -154 03/04/2010 112 01/07/2010 31/08/2010 -133 05/05/2010 65 01/09/2010 31/10/2010 -154 06/07/2010 34 01/11/2010 31/12/2010 -112 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Im getting -12. Its should be 12 + 26 +85 = 123. all these values are between 01/01/10 and 28/02/10. any solution? cheers dave |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum value between two dates and copy to new cell
On Mar 7, 7:06*pm, Roger Govier
wrote: Hi Dave I'm sorry, I just looked at the logic of the formulae, without looking at your data Change Bob's formula to =SUMIF(A:A,""&C2,B:B)-SUMIF(A:A,"="&D2,B:B) or change Joel's formula to =SUMPRODUCT(--($A$2:$A$11=$C2),--($A$2:$A$11<=$D2),B$2:B$11) -- Regards Roger Govier dave wrote: On Mar 7, 6:00 pm, Roger Govier wrote: Hi Dave Bob inadvertently omitted the - sign between his 2 Sumifs =SUMIF(C:C,"="&A2,B:B)-SUMIF(D:D,""&A2,B:B) equally, Joel's formula should work fine. You might need to extend the ranges. If you are still getting error's, then there must be something wrong with your data. Are they true Excel dates, or Text dates? -- Regards Roger Govier dave wrote: On Mar 7, 3:36 pm, "Bob Phillips" wrote: =SUMIF(C:C,"="&A2,B:B)SUMIF(D:D,""&A2,B:B) -- HTH Bob "dave" wrote in message .... Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, * * * A * * * * * * * * *B * * * C D * * * * * * * *E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Thanks in advance. dave- Hide quoted text - - Show quoted text - hI Guys, Im getting an error value. I guess i've provided wrong explain and its confusing. Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to the date. C1 is 01/01/10 and D1 is 28/02/10. so all the value between these dates should be sum and display in column E. * * * * A * * * * * *B * * * * * * * C D * * * * * * * * * *E 04/01/2010 * * *12 * * *01/01/2010 * * *28/02/2010 02/04/2010 * * *133 * * 01/03/2010 * * *30/04/2010 05/06/2010 * * *154 * * 01/05/2010 * * *30/06/2010 03/04/2010 * * *112 * * 01/07/2010 * * *31/08/2010 05/05/2010 * * *65 * * *01/09/2010 * * *31/10/2010 06/07/2010 * * *34 * * *01/11/2010 * * *31/12/2010 02/01/2010 * * *26 02/03/2010 * * *25 14/02/2010 * * *85 17/08/2010 * * *99 Thank you Dave- Hide quoted text - - Show quoted text - Hi Roger, Below is what is the output i received. * * * A * * * * * * * * * * * *B C * * * * * * * * * * * * * * D * * * * * * * * * * * * *E 04/01/2010 12 * * *01/01/2010 * * *28/02/2010 * * *-12 02/04/2010 133 * * 01/03/2010 * * *30/04/2010 * * *-133 05/06/2010 154 * * 01/05/2010 * * *30/06/2010 * * *-154 03/04/2010 112 * * 01/07/2010 * * *31/08/2010 * * *-133 05/05/2010 65 * * *01/09/2010 * * *31/10/2010 * * *-154 06/07/2010 34 * * *01/11/2010 * * *31/12/2010 * * *-112 02/01/2010 26 02/03/2010 25 14/02/2010 85 17/08/2010 99 Im getting -12. Its should be 12 + 26 +85 = 123. all these values are between 01/01/10 and 28/02/10. any solution? cheers dave- Hide quoted text - - Show quoted text - Hi Roger, Its working absolutely perfect. thanks alot mate. Thank you dave |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy and Pasting Dates | Excel Discussion (Misc queries) | |||
| how can I copy dates, etc. without them automatically increasing | Excel Discussion (Misc queries) | |||
| Auto copy dates from one cell to another | Excel Discussion (Misc queries) | |||
| the dates on cell format make different dates. | New Users to Excel | |||
| Copy and paste text and dates | Excel Discussion (Misc queries) | |||
Linear Mode
