Fiscal Year Formula

mePenny

unread,

Nov 24, 2009, 7:18:01 AM11/24/09

to

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

unread,

Nov 24, 2009, 8:06:33 AM11/24/09

to

=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

"mePenny" <meP...@discussions.microsoft.com> wrote in message
news:15225413-3B76-4359...@microsoft.com...

David Biddulph

unread,

Nov 24, 2009, 8:12:41 AM11/24/09

to

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 messagenews:4b0bd...@glkas0286.greenlnk.net...>=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>> "mePenny" <meP...@discussions.microsoft.com> wrote in messagenews:15225413-3B76-4359...@microsoft.com...>>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

unread,

Nov 24, 2009, 8:25:47 AM11/24/09

to

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:

--
David Biddulph

> --
> David Biddulph

--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message

news:4b0bdb9d$1...@glkas0286.greenlnk.net...

mePenny

unread,

Nov 24, 2009, 10:11:01 AM11/24/09

to

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

"David Biddulph" wrote:

> .
>

David Biddulph

unread,

Nov 24, 2009, 10:24:20 AM11/24/09

to

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" <meP...@discussions.microsoft.com> wrote in message

news:8C8AD4D8-6071-4E8B...@microsoft.com...

Joe User

unread,

Nov 24, 2009, 10:29:13 AM11/24/09

to

"mePenny" <meP...@discussions.microsoft.com> 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" <meP...@discussions.microsoft.com> wrote in message

news:15225413-3B76-4359...@microsoft.com...

mePenny

unread,

Nov 24, 2009, 12:31:02 PM11/24/09

to

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

Penny

"David Biddulph" wrote:

> .
>