Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using a date range
I currently have this formula in a spreadsheet:
=SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) Essentially it counts data based on three criteria, the first being if the data includes a date in 2007. My company changed from a fiscal year to a calendar year. Is there a way I can modify the above formula to go from counting if a date included "2007" to counting if a date included July 1, 2007 - June 30, 2008???? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using a date range
=SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(MONTH(Roster!$E$2:$E$646)=7),
--(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) +SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2008), --(MONTH(Roster!$E$2:$E$646)<=6), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) -- David Biddulph "jbressma" wrote in message ... I currently have this formula in a spreadsheet: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) Essentially it counts data based on three criteria, the first being if the data includes a date in 2007. My company changed from a fiscal year to a calendar year. Is there a way I can modify the above formula to go from counting if a date included "2007" to counting if a date included July 1, 2007 - June 30, 2008???? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using a date range
David - thanks for the response. Unfortunately it does not work. I believe
it is counting everything greater than July 2007 and everything less than June 2008. The backend data has data going back to 2001 so I think its not exactly catching data within that range. "David Biddulph" wrote: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(MONTH(Roster!$E$2:$E$646)=7), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) +SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2008), --(MONTH(Roster!$E$2:$E$646)<=6), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) -- David Biddulph "jbressma" wrote in message ... I currently have this formula in a spreadsheet: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) Essentially it counts data based on three criteria, the first being if the data includes a date in 2007. My company changed from a fiscal year to a calendar year. Is there a way I can modify the above formula to go from counting if a date included "2007" to counting if a date included July 1, 2007 - June 30, 2008???? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using a date range
It should count everything in 2007 from July onwards, and add to that
everything in 2008 up to and including June. Isn't that what you wanted? Rows where the date in column E is in 2001 should not appear in the result of that formula. -- David Biddulph "jbressma" wrote in message ... David - thanks for the response. Unfortunately it does not work. I believe it is counting everything greater than July 2007 and everything less than June 2008. The backend data has data going back to 2001 so I think its not exactly catching data within that range. "David Biddulph" wrote: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(MONTH(Roster!$E$2:$E$646)=7), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) +SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2008), --(MONTH(Roster!$E$2:$E$646)<=6), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) -- David Biddulph "jbressma" wrote in message ... I currently have this formula in a spreadsheet: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) Essentially it counts data based on three criteria, the first being if the data includes a date in 2007. My company changed from a fiscal year to a calendar year. Is there a way I can modify the above formula to go from counting if a date included "2007" to counting if a date included July 1, 2007 - June 30, 2008???? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using a date range
You are correct -- thank you very much for the help. I was pulling out my
hair with this one!! "David Biddulph" wrote: It should count everything in 2007 from July onwards, and add to that everything in 2008 up to and including June. Isn't that what you wanted? Rows where the date in column E is in 2001 should not appear in the result of that formula. -- David Biddulph "jbressma" wrote in message ... David - thanks for the response. Unfortunately it does not work. I believe it is counting everything greater than July 2007 and everything less than June 2008. The backend data has data going back to 2001 so I think its not exactly catching data within that range. "David Biddulph" wrote: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(MONTH(Roster!$E$2:$E$646)=7), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) +SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2008), --(MONTH(Roster!$E$2:$E$646)<=6), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) -- David Biddulph "jbressma" wrote in message ... I currently have this formula in a spreadsheet: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) Essentially it counts data based on three criteria, the first being if the data includes a date in 2007. My company changed from a fiscal year to a calendar year. Is there a way I can modify the above formula to go from counting if a date included "2007" to counting if a date included July 1, 2007 - June 30, 2008???? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using a date range
Try this:
=SUMPRODUCT(--(Roster!$E$2:$E$646=DATE(2007,7,1)),--(Roster!$E$2:$E$646<=DATE(2008,6,30)),--(Roster!$C$2:$C$646=B8),--(Roster!$H$2:$H$646="Non-Exp")) -- Biff Microsoft Excel MVP "jbressma" wrote in message ... I currently have this formula in a spreadsheet: =SUMPRODUCT(--(YEAR(Roster!$E$2:$E$646)=2007), --(Roster!$C$2:$C$646=B8), --(Roster!$H$2:$H$646="Non-Exp")) Essentially it counts data based on three criteria, the first being if the data includes a date in 2007. My company changed from a fiscal year to a calendar year. Is there a way I can modify the above formula to go from counting if a date included "2007" to counting if a date included July 1, 2007 - June 30, 2008???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
IF Date range formula | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
how do i use the sum if formula with a date range? | Excel Worksheet Functions |