Count function help

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi,

I have a series of cells, A1 - A10. These cells may be populated with
numbers, either 1 or 2. I would like to be able to count how many of
each number there are, say 6 cells contain 1 and 4 cells contain 2.
Depending on which is the largest - in this case the cells containing
1 - will determine the formatting of a third cell.

I have have had a look at the COUNT and COUNTIF functions, but these
don't seem to fit the bill.

Any help or suggestions how to do this would be very much appreciated.


--
Cheers

Peter

Remove the INVALID to reply
 
Peter,

=COUNTIF(A1:A10,1)

counts the number of cells with a 1 in.

How will it determine the formatting?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Countif absolutely does what you describe

=Countif(A1:A10,1)
=Countif(A1:A10,2)

=if(countif(A1:A10,1)>countif(A1:A10,2),1,2)
 
Peter

COUNTIF will work.

To find the number of 1's enter in C1 =COUNTIF(A1:A10,1)

To format C1 dependant upon the results of the COUNTIF of A1:A10 you can use
use Conditional Formatting.

Format>Conditional Formatting>Formula is:

Condition 1 =COUNTIF(A1:A10,1)>5 Select a Format

Condition 2 =COUNTIF(A1:A10,2)>5 Select a Format

If they are tied at 5 each??

Condition 3 =COUNTIF(A1:A10,1)=COUNTIF(A1:A10,2) Select a Format

Gord Dibben XL2002
 
Peter,

=COUNTIF(A1:A10,1)

counts the number of cells with a 1 in.

How will it determine the formatting?
Click to expand...

Thanks Bob,

That's what I needed.

Sorry if I wasn't clear - it won't directly determine a format, but
what it will do, depending on what the majority is, either cells with
1 or cells with 2, will determine the contents of another cell - not
its formatting - bit woolly of me.


--
Cheers

Peter

Remove the INVALID to reply
 
Countif absolutely does what you describe

=Countif(A1:A10,1)
=Countif(A1:A10,2)

=if(countif(A1:A10,1)>countif(A1:A10,2),1,2)
Click to expand...


Many thanks Tom
--
Cheers

Peter

Remove the INVALID to reply
 
Peter

COUNTIF will work.

To find the number of 1's enter in C1 =COUNTIF(A1:A10,1)

To format C1 dependant upon the results of the COUNTIF of A1:A10 you can use
use Conditional Formatting.

Format>Conditional Formatting>Formula is:

Condition 1 =COUNTIF(A1:A10,1)>5 Select a Format

Condition 2 =COUNTIF(A1:A10,2)>5 Select a Format

If they are tied at 5 each??

Condition 3 =COUNTIF(A1:A10,1)=COUNTIF(A1:A10,2) Select a Format

Gord Dibben XL2002
Click to expand...

Thanks Gord, There won't be a tie - my final sheet will have an odd
number of cells, not 10


--
Cheers

Peter

Remove the INVALID to reply
 
Peter,

No problem, I just thought there was more needed but I wasn't sure what.

Regards

Bob
 
The following will return 2 if there are more 2's than 1's, 1 otherwise.

=MATCH(MAX(COUNTIF(A1:A10,{1,2})),COUNTIF(A1:A10,{1,2}),0)

What's to happen if there are an equal number of 1's and 2's?

Alan Beban
 
Alan hi..
How can I use the countif function to react to a different colored cell?
My needs are to exclude the contents of a cell based on its color.

Regards
Roger
 
Roger,
if you add the following macro it will count and display in cells C6
and C7 the number of ones and twos that are in a1:a10 (excluding any
cell with a bright yellow background: ie with its interior. color index
property set at 6)
If the code is installed in a standard macro you can run it with a
macro button.
If you install it as a worksheet selection event, it will run each time
you activate the worksheet


Sub CountNumbers()
Dim oCell As Range
Dim ones As Integer
Dim twos As Integer

For Each oCell In Range("a1:a10")
'to exclude counting cells that have a bright yellow
background:
If oCell.Interior.ColorIndex <> 6 Then
If oCell.Value = 1 Then
ones = ones + 1
Else
twos = twos + 1
End If
End If

Next

Range("c6") = ones
Range("c7") = twos
End Sub
 
Roger,

Check out this previous post for a function that will help with your request

http://tinyurl.com/qmct

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top