| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fiscal Year Formula
I have this formula below that deals with finding calendar year. I need a
formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B 6:B100="u")*C6:C100)
or =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C 100) -- David Biddulph "mePenny" wrote in message ... I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
Sorry. Misplaced parentheses in the first formula. It should have been
=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100)--David Biddulph"David Biddulph" <groups [at] biddulph.org.uk wrote in ...= SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)= 7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6 :B100="u")*C6:C100) or=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")* (TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C 6:C100) -- David Biddulph "mePenny" wrote in ...I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
Can anyone give me a clue why occasionally I lose all my line feeds?
What the message ought to say (if it doesn't lose the line feeds again) is: Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in ... =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*( B6:B100="u")*C6:C100) or =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(T EXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6: C100) -- David Biddulph "mePenny" wrote in ... I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100)--David Biddulph"David Biddulph" <groups [at] biddulph.org.uk wrote in ...= SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)= 7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6 :B100="u")*C6:C100) or=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")* (TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C 6:C100) -- David Biddulph "mePenny" wrote in ...I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
David, what is the difference between the two formula's?
"David Biddulph" wrote: Can anyone give me a clue why occasionally I lose all my line feeds? What the message ought to say (if it doesn't lose the line feeds again) is: Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in ... =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*( B6:B100="u")*C6:C100) or =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(T EXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6: C100) -- David Biddulph "mePenny" wrote in ... I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100)--David Biddulph"David Biddulph" <groups [at] biddulph.org.uk wrote in ...= SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)= 7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6 :B100="u")*C6:C100) or=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")* (TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C 6:C100) -- David Biddulph "mePenny" wrote in ...I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
They should both give the same result.
=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100) effectively does (YEAR=2009 and MONTH=7) or (YEAR=2010 and MONTH<=6) =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C 100) formats the date as yyyymm (so November 2009 would be 200911) and then looks for values between 200907 and 201006. -- David Biddulph "mePenny" wrote in message ... David, what is the difference between the two formula's? "David Biddulph" wrote: Can anyone give me a clue why occasionally I lose all my line feeds? What the message ought to say (if it doesn't lose the line feeds again) is: Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in ... =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*( B6:B100="u")*C6:C100) or =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(T EXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6: C100) -- David Biddulph "mePenny" wrote in ... I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100)--David Biddulph"David Biddulph" <groups [at] biddulph.org.uk wrote in ...= SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)= 7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6 :B100="u")*C6:C100) or=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")* (TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C 6:C100) -- David Biddulph "mePenny" wrote in ...I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
"mePenny" wrote:
I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) =SUMPRODUCT((X1<=A6:A100)*(A6:A100<=X2)*(B6:B100=" u"), C6:C100) where X1 is presumed to have the date 7/1/09, and X2 has the date 6/30/10. Alternatively, you can replace X1 and X2 with DATE(2009,7,1) and DATE(2010,6,30) respectively in the formula. ----- original message ----- "mePenny" wrote in message ... I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Fiscal Year Formula
Davind, your awesome thank you for explaining and helping.. helped me a bunch!!
Penny "David Biddulph" wrote: They should both give the same result. =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100) effectively does (YEAR=2009 and MONTH=7) or (YEAR=2010 and MONTH<=6) =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C 100) formats the date as yyyymm (so November 2009 would be 200911) and then looks for values between 200907 and 201006. -- David Biddulph "mePenny" wrote in message ... David, what is the difference between the two formula's? "David Biddulph" wrote: Can anyone give me a clue why occasionally I lose all my line feeds? What the message ought to say (if it doesn't lose the line feeds again) is: Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in ... =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*( B6:B100="u")*C6:C100) or =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(T EXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6: C100) -- David Biddulph "mePenny" wrote in ... I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Sorry. Misplaced parentheses in the first formula. It should have been =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100)--David Biddulph"David Biddulph" <groups [at] biddulph.org.uk wrote in ...= SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)= 7))+((YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6 :B100="u")*C6:C100) or=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")* (TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C 6:C100) -- David Biddulph "mePenny" wrote in ...I have this formula below that deals with finding calendar year. I need a formula that does the same thing but for Fiscal Year (7/1/09 - 6/30/10). =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100) Can anyone help? Would truly appreciate it!!! Thank you mePenny . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calendar year versus fiscal year | Excel Discussion (Misc queries) | |||
| using fiscal year calculations in a formula | Excel Discussion (Misc queries) | |||
| Formula to determine Fiscal Year | Excel Discussion (Misc queries) | |||
| Help dealing with a fiscal year rather than a calendar year | Excel Worksheet Functions | |||
| Formula for Fiscal Year Conditional Formatting | Excel Discussion (Misc queries) | |||
Linear Mode
