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

> 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

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

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

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

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

1. regexp for reg language that has an even number of a's and b's

2. Finding the Value (of text and numbers) between N/A's

3. $sth->rows, doesn't return the correct number of rows return

4. Number of columns in a query affects the number of rows returned?

5. function - find first number in row > 0, return cell column number

6. R: function - find first number in row > 0, return cell column number

7. command to return the row number or cell number in excel?

8. total numbers of numbers input , largest number and smallest number and average of numbers

9. equation that will filter out #N/A's

11. Conditional count in range w/#N?A's

13. "Matching" column A's values in column B

14. Capture image of App#A's window from App#B

4 post • Page:**1** of **1**