Formula using a date range

[jbressma]

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

 

[David Biddulph]

=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"))

 

[jbressma]

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]

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.

 

[T. Valko]

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"))

 

[jbressma]

You are correct -- thank you very much for the help. I was pulling out my
hair with this one!!