T,

With your names in column A and dates in column B, starting in row 2 (with headers in row 1), and
the effective date entered into C1 (so that you don't have to rush and can easoly review previous
weeks), enter this formula into cell C2:

=IF(AND(B2<=$C$1,B2=$C$1-35),
SUMPRODUCT(($A$2:$A$15=A2)*($B$2:$B$15=$C$1-35)*($B$2:$B$15<=$C$1)),"")

All on one line. Change all of the $15's to reflect your actual last value's row number.

HTH,
Bernie
MS Excel MVP


"onesidered" wrote in message
...

I work in Recreation and thought that excel might be able to help us.
Here is the background.

I am running a sports league. Each week teams play and results are
sent to us to post. Players that were penalized during the game are
put onto a list and if they are penalized 3 times within 5 weeks they
are suspended.

Here is my question.

Is there a formula in excel that would allow me to determine the 3 time
offenders automatically.

Example:

Ted 5/29
Jay 6/5
David 6/5
John 6/12
Gustavo 6/12
Greg 6/12
Ernesto 6/26
Robert 6/26
Gustavo 6/26
Joel 7/10
Gustavo 7/10
Greg 7/10
Jose 7/24
Gustavo 7/31

Each of these players were penalized on these dates. For example
purposes look at Gustavo. He has been penalized at least 3 times in a
5 week period. He would be suspended for 1 week because of it.

Usually I would have to compare this list each week with a calendar and
manually add/edit.

Any help would greatly be appreciated...

Please email me with any questions.


Thank you,
T. Larson


--
onesidered
------------------------------------------------------------------------
onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977
View this thread: http://www.excelforum.com/showthread...hreadid=393435