=IF(COUNTIF(I298:AL298,1)<4,"Spare","Team 1")
=IF(COUNTIF(I298:AL298,2)<4,"Spare","Team 2")
=IF(COUNTIF(I298:AL298,3)<4,"Spare","Team 3")
=IF(COUNTIF(I298:AL298,4)<4,"Spare","Team 4")
=IF(COUNTIF(I298:AL298,5)<4,"Spare","Team 5")
=IF(COUNTIF(I298:AL298,6)<4,"Spare","Team 6")
=IF(COUNTIF(I298:AL298,7)<4,"Spare","Team 7")
=IF(COUNTIF(I298:AL298,8)<4,"Spare","Team 8")
=IF(COUNTIF(I298:AL298,9)<4,"Spare","Team 9")
=IF(COUNTIF(I298:AL298,10)<4,"Spare","Team 10")
=IF(COUNTIF(I298:AL298,11)<4,"Spare","Team 11")
=IF(COUNTIF(I298:AL298,12)<4,"Spare","Team 12")
=IF(COUNTIF(I298:AL298,13)<4,"Spare","Team 13")
=IF(COUNTIF(I298:AL298,14)<4,"Spare","Team 14")
=IF(COUNTIF(I298:AL298,15)<4,"Spare","Team 15")
=IF(COUNTIF(I298:AL298,16)<4,"Spare","Team 16")
=IF(COUNTIF(I298:AL298,17)<4,"Spare","Team 17")
=IF(COUNTIF(I298:AL298,18)<4,"Spare","Team 18")

On Sep 4, 3:54*pm, Ephraim <rick.mal...@gmail.com> wrote:
> =IF(COUNTIF(I298:AL298,1)<4,"Spare","Team * 1")
> =IF(COUNTIF(I298:AL298,2)<4,"Spare","Team * 2")
> =IF(COUNTIF(I298:AL298,3)<4,"Spare","Team * 3")
> =IF(COUNTIF(I298:AL298,4)<4,"Spare","Team * 4")
> =IF(COUNTIF(I298:AL298,5)<4,"Spare","Team * 5")
> =IF(COUNTIF(I298:AL298,6)<4,"Spare","Team * 6")
> =IF(COUNTIF(I298:AL298,7)<4,"Spare","Team * 7")
> =IF(COUNTIF(I298:AL298,8)<4,"Spare","Team * 8")
> =IF(COUNTIF(I298:AL298,9)<4,"Spare","Team * 9")
> =IF(COUNTIF(I298:AL298,10)<4,"Spare","Team * 10")
> =IF(COUNTIF(I298:AL298,11)<4,"Spare","Team * 11")
> =IF(COUNTIF(I298:AL298,12)<4,"Spare","Team * 12")
> =IF(COUNTIF(I298:AL298,13)<4,"Spare","Team * 13")
> =IF(COUNTIF(I298:AL298,14)<4,"Spare","Team * 14")
> =IF(COUNTIF(I298:AL298,15)<4,"Spare","Team * 15")
> =IF(COUNTIF(I298:AL298,16)<4,"Spare","Team * 16")
> =IF(COUNTIF(I298:AL298,17)<4,"Spare","Team * 17")
> =IF(COUNTIF(I298:AL298,18)<4,"Spare","Team * 18")

What does it mean to "combine into one cell"?

Explain the logic that you want.

For example, do you want to return lowest-numbered team that meets the
condition, namely >=4 occurrences of the team number in I298:AL298?

PS: For broader participation, you might want to post this inquiry or
future inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/...ry/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

On Sep 4, 5:54*pm, Ephraim <rick.mal...@gmail.com> wrote:
> =IF(COUNTIF(I298:AL298,1)<4,"Spare","Team * 1")
> =IF(COUNTIF(I298:AL298,2)<4,"Spare","Team * 2")
> =IF(COUNTIF(I298:AL298,3)<4,"Spare","Team * 3")
> =IF(COUNTIF(I298:AL298,4)<4,"Spare","Team * 4")
> =IF(COUNTIF(I298:AL298,5)<4,"Spare","Team * 5")
> =IF(COUNTIF(I298:AL298,6)<4,"Spare","Team * 6")
> =IF(COUNTIF(I298:AL298,7)<4,"Spare","Team * 7")
> =IF(COUNTIF(I298:AL298,8)<4,"Spare","Team * 8")
> =IF(COUNTIF(I298:AL298,9)<4,"Spare","Team * 9")
> =IF(COUNTIF(I298:AL298,10)<4,"Spare","Team * 10")
> =IF(COUNTIF(I298:AL298,11)<4,"Spare","Team * 11")
> =IF(COUNTIF(I298:AL298,12)<4,"Spare","Team * 12")
> =IF(COUNTIF(I298:AL298,13)<4,"Spare","Team * 13")
> =IF(COUNTIF(I298:AL298,14)<4,"Spare","Team * 14")
> =IF(COUNTIF(I298:AL298,15)<4,"Spare","Team * 15")
> =IF(COUNTIF(I298:AL298,16)<4,"Spare","Team * 16")
> =IF(COUNTIF(I298:AL298,17)<4,"Spare","Team * 17")
> =IF(COUNTIF(I298:AL298,18)<4,"Spare","Team * 18")
I also have no idea what you want but this function placed in a
REGULAR macro module with =wt(i298:al298) will show the teams if >=4
for each number 1-18 Teams,2,3

Function wt(x As Range)
Dim i As Double
dim ms as string
For i = 1 To 18
If Application.CountIf(x, i) >= 4 Then _
ms = ms & "," & i
Next i
wt = "Teams" & ms
End Function

On Sep 5, 8:00*am, Don Guillett Excel MVP <dguille...@austin.rr.com>
wrote:
> On Sep 4, 5:54*pm, Ephraim <rick.mal...@gmail.com> wrote:
>
>
>
> > =IF(COUNTIF(I298:AL298,1)<4,"Spare","Team * 1")
> > =IF(COUNTIF(I298:AL298,2)<4,"Spare","Team * 2")
> > =IF(COUNTIF(I298:AL298,3)<4,"Spare","Team * 3")
> > =IF(COUNTIF(I298:AL298,4)<4,"Spare","Team * 4")
> > =IF(COUNTIF(I298:AL298,5)<4,"Spare","Team * 5")
> > =IF(COUNTIF(I298:AL298,6)<4,"Spare","Team * 6")
> > =IF(COUNTIF(I298:AL298,7)<4,"Spare","Team * 7")
> > =IF(COUNTIF(I298:AL298,8)<4,"Spare","Team * 8")
> > =IF(COUNTIF(I298:AL298,9)<4,"Spare","Team * 9")
> > =IF(COUNTIF(I298:AL298,10)<4,"Spare","Team * 10")
> > =IF(COUNTIF(I298:AL298,11)<4,"Spare","Team * 11")
> > =IF(COUNTIF(I298:AL298,12)<4,"Spare","Team * 12")
> > =IF(COUNTIF(I298:AL298,13)<4,"Spare","Team * 13")
> > =IF(COUNTIF(I298:AL298,14)<4,"Spare","Team * 14")
> > =IF(COUNTIF(I298:AL298,15)<4,"Spare","Team * 15")
> > =IF(COUNTIF(I298:AL298,16)<4,"Spare","Team * 16")
> > =IF(COUNTIF(I298:AL298,17)<4,"Spare","Team * 17")
> > =IF(COUNTIF(I298:AL298,18)<4,"Spare","Team * 18")
>
> I also have no idea what you want but this function placed in a
> REGULAR macro module with =wt(i298:al298) will show the teams if >=4
> for each number 1-18 * * * * Teams,2,3
>
> Function wt(x As Range)
> Dim i As Double
> dim ms as string
> For i = 1 To 18
> If Application.CountIf(x, i) >= 4 Then _
> ms = ms & "," & i
> Next i
> wt = "Teams" & ms
> End Function- Hide quoted text -
>
> - Show quoted text -

In a nutshell, if I were to put each one of the above functions in
cell H298 one at a time then each function would work for a given
number from 1-18 but I need it to work for all numbers from 1-18.

I think as a non power user of Excel that I may be trying to do
something that can't be done with COUNTIF.

I really want to loop through each of the numbers 1 to 18 using the
COUNTIF function. As soon as any one of the numbers 1-18 appear for
the fourth time then the cell containing the formula would display the
corresponding team name. As long as any one of the numbers from 1-18
appear in cell I298:AL298 less than 4 times then the cell containing
the formula would display "Spare" .

I would continue entering numbers from 1-18 in cells I298:AL298 until
I see that the value of the cell containing the formula no longer
displays "Spare".

In other words a player can play for any of the 18 teams until they
have played for a team for 4 times at which point they can no longer
play for any other team but are assigned to the team they have played
with for the fourth time.

I would love to do this with a function but if not a macro would have
to do. Some users of the spreadsheet do not want to run macros.

This spreadsheet is being used by users with Excel 2003 and Excel
2007.

Thanks for your help and I hope I've explained it well enough.

On Sep 5, 7:48*am, Ephraim <rick.mal...@gmail.com> wrote:
> On Sep 5, 8:00*am, Don Guillett Excel MVP <dguille...@austin.rr.com>
> wrote:
>
>
>
>
>
> > On Sep 4, 5:54*pm, Ephraim <rick.mal...@gmail.com> wrote:
>
> > > =IF(COUNTIF(I298:AL298,1)<4,"Spare","Team * 1")
> > > =IF(COUNTIF(I298:AL298,2)<4,"Spare","Team * 2")
> > > =IF(COUNTIF(I298:AL298,3)<4,"Spare","Team * 3")
> > > =IF(COUNTIF(I298:AL298,4)<4,"Spare","Team * 4")
> > > =IF(COUNTIF(I298:AL298,5)<4,"Spare","Team * 5")
> > > =IF(COUNTIF(I298:AL298,6)<4,"Spare","Team * 6")
> > > =IF(COUNTIF(I298:AL298,7)<4,"Spare","Team * 7")
> > > =IF(COUNTIF(I298:AL298,8)<4,"Spare","Team * 8")
> > > =IF(COUNTIF(I298:AL298,9)<4,"Spare","Team * 9")
> > > =IF(COUNTIF(I298:AL298,10)<4,"Spare","Team * 10")
> > > =IF(COUNTIF(I298:AL298,11)<4,"Spare","Team * 11")
> > > =IF(COUNTIF(I298:AL298,12)<4,"Spare","Team * 12")
> > > =IF(COUNTIF(I298:AL298,13)<4,"Spare","Team * 13")
> > > =IF(COUNTIF(I298:AL298,14)<4,"Spare","Team * 14")
> > > =IF(COUNTIF(I298:AL298,15)<4,"Spare","Team * 15")
> > > =IF(COUNTIF(I298:AL298,16)<4,"Spare","Team * 16")
> > > =IF(COUNTIF(I298:AL298,17)<4,"Spare","Team * 17")
> > > =IF(COUNTIF(I298:AL298,18)<4,"Spare","Team * 18")
>
> > I also have no idea what you want but this function placed in a
> > REGULAR macro module with =wt(i298:al298) will show the teams if >=4
> > for each number 1-18 * * * * Teams,2,3
>
> > Function wt(x As Range)
> > Dim i As Double
> > dim ms as string
> > For i = 1 To 18
> > If Application.CountIf(x, i) >= 4 Then _
> > ms = ms & "," & i
> > Next i
> > wt = "Teams" & ms
> > End Function- Hide quoted text -
>
> > - Show quoted text -
>
> In a nutshell, if I were to put each one of the above functions in
> cell H298 one at a time then each function would work for a given
> number from 1-18 but I need it to work for all numbers from 1-18.
>
> I think as a non power user of Excel that I may be trying to do
> something that can't be done with COUNTIF.
>
> I really want to loop through each of the numbers 1 to 18 using the
> COUNTIF function. As soon as any one of the numbers 1-18 appear for
> the fourth time then the cell containing the formula would display the
> corresponding team name. As long as any one of the numbers from 1-18
> appear in cell I298:AL298 less than 4 times then the cell containing
> the formula would display "Spare" .
>
> I would continue entering numbers from 1-18 in cells I298:AL298 until
> I see that the value of the cell containing the formula no longer
> displays "Spare".
>
> In other words a player can play for any of the 18 teams until they
> have played for a team for 4 times at which point they can no longer
> play for any other team but are assigned to the team they have played
> with for the fourth time.
>
> I would love to do this with a function but if not a macro would have
> to do. Some users of the spreadsheet do not want to run macros.
>
> This spreadsheet is being used by users with Excel 2003 and Excel
> 2007.
>
> Thanks for your help and I hope I've explained it well enough.- Hide quoted text -
>
> - Show quoted text -

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

Well, if you really wanted to you could do this in one cell:

=IF(COUNTIF(I298:AL298,1)>4,"Team 1","") &
IF(COUNTIF(I298:AL298,2)>4,"Team 2","") &
IF(COUNTIF(I298:AL298,3)>4,"Team 3","") & ...

and so on up to 18 teams.

and in an adjacent cell you could have:

=IF(adj_cell="","Spare",adj_cell)

Then you could hide the first column.

Hope this helps.

Pete

On Sep 4, 11:54*pm, Ephraim <rick.mal...@gmail.com> wrote:
> =IF(COUNTIF(I298:AL298,1)<4,"Spare","Team * 1")
> =IF(COUNTIF(I298:AL298,2)<4,"Spare","Team * 2")
> =IF(COUNTIF(I298:AL298,3)<4,"Spare","Team * 3")
> =IF(COUNTIF(I298:AL298,4)<4,"Spare","Team * 4")
> =IF(COUNTIF(I298:AL298,5)<4,"Spare","Team * 5")
> =IF(COUNTIF(I298:AL298,6)<4,"Spare","Team * 6")
> =IF(COUNTIF(I298:AL298,7)<4,"Spare","Team * 7")
> =IF(COUNTIF(I298:AL298,8)<4,"Spare","Team * 8")
> =IF(COUNTIF(I298:AL298,9)<4,"Spare","Team * 9")
> =IF(COUNTIF(I298:AL298,10)<4,"Spare","Team * 10")
> =IF(COUNTIF(I298:AL298,11)<4,"Spare","Team * 11")
> =IF(COUNTIF(I298:AL298,12)<4,"Spare","Team * 12")
> =IF(COUNTIF(I298:AL298,13)<4,"Spare","Team * 13")
> =IF(COUNTIF(I298:AL298,14)<4,"Spare","Team * 14")
> =IF(COUNTIF(I298:AL298,15)<4,"Spare","Team * 15")
> =IF(COUNTIF(I298:AL298,16)<4,"Spare","Team * 16")
> =IF(COUNTIF(I298:AL298,17)<4,"Spare","Team * 17")
> =IF(COUNTIF(I298:AL298,18)<4,"Spare","Team * 18")

Hi Ephraim

Firstly, create a named range referring to your source data.
Insert>name>Define
Name myData
Refers to $I$298:$AL$298

N.B. Include the sheet name in the Refers to definition.

Then just set yourself up a small table on a new sheet with the numbers 1 to
18 in cells A1 to A18 of a new sheet.
Then in B1 enter
=IF(COUNTIF(myData)<4,"Spare","Team " & A1)
Copy down through cells B2:B18

--

Regards
Roger Govier

"Ephraim" <(E-Mail Removed)> wrote in message
news:41b37a05-58c0-4e29-9565-(E-Mail Removed)...
> On Sep 5, 8:00 am, Don Guillett Excel MVP <dguille...@austin.rr.com>
> wrote:
>> On Sep 4, 5:54 pm, Ephraim <rick.mal...@gmail.com> wrote:
>>
>>
>>
>> > =IF(COUNTIF(I298:AL298,1)<4,"Spare","Team 1")
>> > =IF(COUNTIF(I298:AL298,2)<4,"Spare","Team 2")
>> > =IF(COUNTIF(I298:AL298,3)<4,"Spare","Team 3")
>> > =IF(COUNTIF(I298:AL298,4)<4,"Spare","Team 4")
>> > =IF(COUNTIF(I298:AL298,5)<4,"Spare","Team 5")
>> > =IF(COUNTIF(I298:AL298,6)<4,"Spare","Team 6")
>> > =IF(COUNTIF(I298:AL298,7)<4,"Spare","Team 7")
>> > =IF(COUNTIF(I298:AL298,8)<4,"Spare","Team 8")
>> > =IF(COUNTIF(I298:AL298,9)<4,"Spare","Team 9")
>> > =IF(COUNTIF(I298:AL298,10)<4,"Spare","Team 10")
>> > =IF(COUNTIF(I298:AL298,11)<4,"Spare","Team 11")
>> > =IF(COUNTIF(I298:AL298,12)<4,"Spare","Team 12")
>> > =IF(COUNTIF(I298:AL298,13)<4,"Spare","Team 13")
>> > =IF(COUNTIF(I298:AL298,14)<4,"Spare","Team 14")
>> > =IF(COUNTIF(I298:AL298,15)<4,"Spare","Team 15")
>> > =IF(COUNTIF(I298:AL298,16)<4,"Spare","Team 16")
>> > =IF(COUNTIF(I298:AL298,17)<4,"Spare","Team 17")
>> > =IF(COUNTIF(I298:AL298,18)<4,"Spare","Team 18")
>>
>> I also have no idea what you want but this function placed in a
>> REGULAR macro module with =wt(i298:al298) will show the teams if >=4
>> for each number 1-18 Teams,2,3
>>
>> Function wt(x As Range)
>> Dim i As Double
>> dim ms as string
>> For i = 1 To 18
>> If Application.CountIf(x, i) >= 4 Then _
>> ms = ms & "," & i
>> Next i
>> wt = "Teams" & ms
>> End Function- Hide quoted text -
>>
>> - Show quoted text -
>
> In a nutshell, if I were to put each one of the above functions in
> cell H298 one at a time then each function would work for a given
> number from 1-18 but I need it to work for all numbers from 1-18.
>
> I think as a non power user of Excel that I may be trying to do
> something that can't be done with COUNTIF.
>
> I really want to loop through each of the numbers 1 to 18 using the
> COUNTIF function. As soon as any one of the numbers 1-18 appear for
> the fourth time then the cell containing the formula would display the
> corresponding team name. As long as any one of the numbers from 1-18
> appear in cell I298:AL298 less than 4 times then the cell containing
> the formula would display "Spare" .
>
> I would continue entering numbers from 1-18 in cells I298:AL298 until
> I see that the value of the cell containing the formula no longer
> displays "Spare".
>
> In other words a player can play for any of the 18 teams until they
> have played for a team for 4 times at which point they can no longer
> play for any other team but are assigned to the team they have played
> with for the fourth time.
>
> I would love to do this with a function but if not a macro would have
> to do. Some users of the spreadsheet do not want to run macros.
>
> This spreadsheet is being used by users with Excel 2003 and Excel
> 2007.
>
> Thanks for your help and I hope I've explained it well enough.
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5418 (20100902) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) __________

The message was checked by ESET Smart Security.

http://www.eset.com