count number of times text appears
 

I have a column with the values red, amber and green, in text format, not
just a colour in the cell.
I want to count the values and in the calculation field, show the value
which occurs most frequently.
e.g.
amber
amber
red
green
amber

the calcualtion field should return amber

count number of times text appears
 

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ,rng),0))

it is an array formula, so commit with Ctrl-Shift-enter, not just Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"MMcQ" wrote in message
...
I have a column with the values red, amber and green, in text format, not
just a colour in the cell.
I want to count the values and in the calculation field, show the value
which occurs most frequently.
e.g.
amber
amber
red
green
amber

the calcualtion field should return amber

count number of times text appears
 

Try this:

With
A1:A20 containing the values (text, numeric, or blanks)

This ARRAY FORMULA returns the most occurring non-blank value
B1: =INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),CO UNTIF(A1:A20,A1:A20),0))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MMcQ" wrote:

I have a column with the values red, amber and green, in text format, not
just a colour in the cell.
I want to count the values and in the calculation field, show the value
which occurs most frequently.
e.g.
amber
amber
red
green
amber

the calcualtion field should return amber

count number of times text appears
 

What if the data contains more than one value occurring the most? For
example, if the data contains the following...

amber
amber
red
red
green
blue
yellow
yellow
brown

....what result do you expect?

In article ,
MMcQ wrote:

I have a column with the values red, amber and green, in text format, not
just a colour in the cell.
I want to count the values and in the calculation field, show the value
which occurs most frequently.
e.g.
amber
amber
red
green
amber

the calcualtion field should return amber