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

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

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

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

> D > F > H
> 1 Player Name gt;eam Name 18 4 >> >8 8 6 18
> 2 John Doe T>am 18
>
> So in this case this player has played for team 18 a total >f 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 statem>nt but I
> thought there must be an easier way. I'd prefer n>t >o use macros but
> would like to >ee>both macr> and plain function solutions.
>
> I'm doing this in MS Excel 2007
>
> Thanks
> Ephraim

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

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

On Sep 4, 2:51m, Don Guillett Excel MVP < XXXX@XXXXX.COM >

> > D > >E F > >> > 1 Player Name e>m>Name 18 4
> >>18>gt;8 6 18
> > 2 John Doe Tea> >8
>
> > So in this case this player has played for team 18 a total of>4>times
> > and can no longer b> a>s>are in the league but must play for team 18
> > for the rest of the s>a>on.
>
> > I thought I might use a lengthy countif or countifs statemen> >ut I
> > thought there must be an easier way. I'd prefer not>to>u>e macros but
> > would like to se> b>t> macro an> >lain funct>on>solutions.
>
> > I'm doing this in MS Excel 2007
>
> > Thanks >> > Ephraim
>
> Your table came out garbled and I suspect there is mor> info >o
> "If desired, send your file to dguillett gmail.com > will only look
> if:
> 1. You send a copy of this>message on an inserted sheet
> 2. You give me the >ewsgroup and the subject line
> 3. You send a clear explanation of what you wa>t >> 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")

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

> > > D > >E>F H >>>>>> 1 Player Name eam>N>m> 18 4
> > > 1> gt;gt;gt;8 6 18
> > > 2 John Doe Team >8> >
> > > So in this case this player has played for team 18 a total of 4>t>m>s
> > > and can no longer be > s>a>e>in the league but must play for team 18
> > > for the rest of the sea>o>.> >
> > > I thought I might use a lengthy countif or countifs statement >u> >
> > > thought there must be an easier way. I'd prefer not t> u>e>m>cros but
> > > would like to see >ot> >a>ro and pl>i> >unction so>ut>o>s.
>
> > > I'm doing this in MS Excel 2007
>
> > > Thanks
> >>>>Ephraim
>
> > Your table came out garbled and I suspect there is more >n>o so
> > if:
> > 1. You send a copy of this m>s>age on an inserted sheet
> > 2. You give me the ne>s>roup and the subject line
> > 3. You send a clear explanation of what you want> >>>>4. You send before/afte> e>amples and expected results."- Hide quoted text -
>
> > - Show quoted t>xt -
>
> 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
> sw>et>
>
> =IF(COUNTIF(\$I\$297:\$AL\$297,18)=4,"Team_18","Spare")- Hide quoted text -
>
> - Sho< quoted text -
Sorry, actually this works better.
=IF(COUNTIF(\$I\$297:\$AL\$297,18)<4,"Spare","Team 18")