Countif until a number occurs four times in a row then return that number

[Ephraim]

Well it's been a while since I've posted here but I've always
appreciated the help that the users here have provided.

I am trying to track the number of times a player in our league has
played as a "spare" for a particular team. Once a player has "spared"
4 times for a team then they are considered to be a member of that
team and can no longer play for any other team.

I am tracking who they played for by entering the "Team Number", in
this case teams are numbered 1 to 18 and once any of the numbers (1 to
18) have been entered 4 times a cell at the beginning of that row
would display the team name.

i.e.
A B C
D E F G H
1 Player Name Team Name 18 4
18 18 6 18
2 John Doe Team 18

So in this case this player has played for team 18 a total of 4 times
and can no longer be a spare in the league but must play for team 18
for the rest of the season.

I thought I might use a lengthy countif or countifs statement but I
thought there must be an easier way. I'd prefer not to use macros but
would like to see both macro and plain function solutions.

I'm doing this in MS Excel 2007

Thanks
Ephraim

 

[Don Guillett Excel MVP]

Well it's been a while since I've posted here but I've always
appreciated the help that the users here have provided.

I am trying to track the number of times a player in our league has
played  as a "spare" for a particular team. Once a player has "spared"
4 times for a team then they are considered to be a member of that
team and can no longer play for any other team.

I am tracking who they played for by entering the "Team Number", in
this case teams are numbered 1 to 18 and once any of the numbers (1 to
18) have been entered 4 times  a cell at the beginning of that row
would display the team name.

i.e.
A                               B                      C
D              E                 F            G           H
1   Player Name        Team Name     18          4
18              18           6           18
2   John Doe             Team 18

So in this case this player has played for team 18 a total of 4 times
and can no longer be a spare in the league but must play for team 18
for the rest of the season.

I thought I might use a lengthy countif or countifs statement but I
thought there must be an easier way. I'd prefer not to use macros but
would like to see both macro and plain function solutions.

I'm doing this in MS Excel 2007

Thanks
Ephraim

Your table came out garbled and I suspect there is more info so
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Ephraim]

Your table came out garbled and I suspect there is more info so
"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text -

I have this formula that works for the number 18. Could I modify it to
work for all numbers between 1 and 18 and still keep it short ans
sweet?

=IF(COUNTIF($I$297:$AL$297,18)=4,"Team_18","Spare")

 

[Ephraim]

I have this formula that works for the number 18. Could I modify it to
work for all numbers between 1 and 18 and still keep it short ans
sweet?

=IF(COUNTIF($I$297:$AL$297,18)=4,"Team_18","Spare")- Hide quoted text-

- Show quoted text -

Sorry, actually this works better.
=IF(COUNTIF($I$297:$AL$297,18)<4,"Spare","Team 18")