Fiscal Year Formula

M

mePenny

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
 
D

David Biddulph

=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")*C6:C100)
 
D

David Biddulph

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 messageor>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)> --> David Biddulph>> "mePenny" <[email protected]> wrote in messagehave 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>>
 
D

David Biddulph

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)
Click to expand...

--
David Biddulph

David Biddulph said:
=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")*C6:C100)
Click to expand...

--
David Biddulph

David Biddulph said:
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
messageor>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)>
in messagehave 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>>
Click to expand...
 
M

mePenny

David, what is the difference between the two formula's?

David Biddulph said:
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)
Click to expand...

--
David Biddulph

David Biddulph said:
=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")*C6:C100)
Click to expand...

--
David Biddulph

David Biddulph said:
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
messageor>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)>
in messagehave 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>>
Click to expand...


.
Click to expand...
 
D

David Biddulph

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")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)
formats the date as yyyymm (so November 2009 would be 200911) and then looks
for values between 200907 and 201006.
--
David Biddulph

mePenny said:
David, what is the difference between the two formula's?

David Biddulph said:
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)
Click to expand...

--
David Biddulph

David Biddulph said:
=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")*C6:C100)
Click to expand...
messageI 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
Click to expand...

--
David Biddulph

David Biddulph said:
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
messageor>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)>
--> David Biddulph>> "mePenny" <[email protected]>
wrote
in messagehave 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>>
Click to expand...


.
Click to expand...
Click to expand...
 
J

Joe User

mePenny said:
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)
Click to expand...

=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 -----
 
M

mePenny

Davind, your awesome thank you for explaining and helping.. helped me a bunch!!

Penny

David Biddulph said:
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")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)
formats the date as yyyymm (so November 2009 would be 200911) and then looks
for values between 200907 and 201006.
--
David Biddulph

mePenny said:
David, what is the difference between the two formula's?

David Biddulph said:
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
message=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")*C6:C100)

--
David Biddulph

messageI 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
messageor>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="u")*C6:C100)>
--> David Biddulph>> "mePenny" <[email protected]>
wrote
in messagehave 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>>



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Averaging Fiscal / Calendar year Fuel 11
calendar year versus fiscal year 4
using fiscal year calculations in a formula 1
Still in need of help 3
fiscal year calculation 3
Calculating Dates Within Fiscal Year 2
How do I convert a date to a fiscal year? 5
Formula to determine Fiscal Year 3

Top