| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating between 2 given dates
Hello
I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) ......where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
One way...
A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
I assume the equal sign here is an oversight...
)-=SUMIF( -- HTH... Jim Thomlinson "T. Valko" wrote: One way... A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
Correction:
=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Removed the equal sign before the 2nd SUMIF. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
Thanks Very much (and noted the comment about different sized ranges). Is
there actually a way of doing the dates within the formula something like this.. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)...ie so that within the formula I list the date before and after that I want to apply? Thanks "T. Valko" wrote: One way... A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
calculate amonts in a sumif that are between 2 dates.
In 99 out of 100 cases when someone says "between 2 dates" what they really mean is greater than or equal to a start date and less than or equal to an end date. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)... The logic of the above would *exclude* the following dates (m/d/y format): 2/1/2008 4/30/2008 Try one of these: =SUMIF('Costs detail'!$Q$2:$Q518,"="&DATE(2008,2,1),'Costs detail'!$O$2:$O$518)-SUMIF('Costs detail'!$Q$2:$Q518,""&DATE(2008,4,30),'Costs detail'!$O$2:$O$518) =SUMPRODUCT(--('Costs detail'!$Q$2:$Q518=DATE(2008,2,1)),--('Costs detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518) -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Thanks Very much (and noted the comment about different sized ranges). Is there actually a way of doing the dates within the formula something like this.. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)...ie so that within the formula I list the date before and after that I want to apply? Thanks "T. Valko" wrote: One way... A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
That perfect. Worked a treat. Much appreciated
"T. Valko" wrote: calculate amonts in a sumif that are between 2 dates. In 99 out of 100 cases when someone says "between 2 dates" what they really mean is greater than or equal to a start date and less than or equal to an end date. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)... The logic of the above would *exclude* the following dates (m/d/y format): 2/1/2008 4/30/2008 Try one of these: =SUMIF('Costs detail'!$Q$2:$Q518,"="&DATE(2008,2,1),'Costs detail'!$O$2:$O$518)-SUMIF('Costs detail'!$Q$2:$Q518,""&DATE(2008,4,30),'Costs detail'!$O$2:$O$518) =SUMPRODUCT(--('Costs detail'!$Q$2:$Q518=DATE(2008,2,1)),--('Costs detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518) -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Thanks Very much (and noted the comment about different sized ranges). Is there actually a way of doing the dates within the formula something like this.. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)...ie so that within the formula I list the date before and after that I want to apply? Thanks "T. Valko" wrote: One way... A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating between 2 given dates
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... That perfect. Worked a treat. Much appreciated "T. Valko" wrote: calculate amonts in a sumif that are between 2 dates. In 99 out of 100 cases when someone says "between 2 dates" what they really mean is greater than or equal to a start date and less than or equal to an end date. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)... The logic of the above would *exclude* the following dates (m/d/y format): 2/1/2008 4/30/2008 Try one of these: =SUMIF('Costs detail'!$Q$2:$Q518,"="&DATE(2008,2,1),'Costs detail'!$O$2:$O$518)-SUMIF('Costs detail'!$Q$2:$Q518,""&DATE(2008,4,30),'Costs detail'!$O$2:$O$518) =SUMPRODUCT(--('Costs detail'!$Q$2:$Q518=DATE(2008,2,1)),--('Costs detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518) -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Thanks Very much (and noted the comment about different sized ranges). Is there actually a way of doing the dates within the formula something like this.. SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts detail'!$O$2:$O$542)...ie so that within the formula I list the date before and after that I want to apply? Thanks "T. Valko" wrote: One way... A8 = start date = 2/1/2008 A9 = end date = 4/30/2008 =SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs detail'!$O$2:$O$518) Note that in your posted formula you have different sized ranges: $Q$2:$Q518 $O$2:$O$542 -- Biff Microsoft Excel MVP "spudsnruf" wrote in message ... Hello I would like to learn how to calculate amonts in a sumif that are between 2 dates. eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542) .....where A8 is currently just a month reference (I mean it looks at cell A8 where I type in the month). So in other words, I'd like it to look up values in another worksheet called Costs Detail, providing the associated dates are between, say 1st Feb and end of April. Any help always please. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating dates | Excel Worksheet Functions | |||
| CALCULATING DATES | New Users to Excel | |||
| Calculating dates | Excel Worksheet Functions | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
| I need help calculating dates? | Excel Worksheet Functions | |||
Linear Mode
