Can these countif functions all be combined in one cell?

E

Ephraim

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

joeu2004

=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")
Click to expand...

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/Forums/en-US/category/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.
 
D

Don Guillett Excel MVP

=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")
Click to expand...
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
 
E

Ephraim

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 -
Click to expand...

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

Don Guillett Excel MVP

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 -
Click to expand...

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

Pete_UK

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
 
R

Roger Govier

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 said:
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
Click to expand...

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

The message was checked by ESET Smart Security.

http://www.eset.com
 
Top