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????

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????

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????

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????

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????

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????