Get the most recurring value listed in Microsoft Excel

[Aza]

I have a column with the values red, amber and green. 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 calculation field should return amber.

[-Deven-]

Excel doesn't have a built in way of counting cells by color. You will need to find a proper macro for this. Only then you can get the expected result. You can try searching in the official help forums of Microsoft Excel for more information.

[JyotiT]

I actually have the words in the column as well as them color coding based on conditional formatting, can I do it using the text. There are some sample formula you can try out, they are =COUNTIF(A1:A100,"amber"), =COUNTIF(A1:A100,"red") and =COUNTIF(A1:A100,"green").

[Go!pee]

I don't quite understand your original post - if you just return the largest number, how will you know which color it relates to. You could put "amber", "green" and "red" in cells C1, C2 and C3, then in D1: =COUNTIF(A$1:A$100,C1) and copy this to D2 and D3 to show you all the values (adjust range as necessary). If you really want to you could try: =MAX(COUNTIF(A$1:A$100,"amber"),COUNTIF(A$1:A$100, "red"),COUNTIF(A$1:A $100,"green")). But this will only give you the maximum number.

[Panop]

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MODE(MATCH(rng,rng,0))). There must be a most frequent value for this to work. In other words, if all entries are unique you'll get an error. Also, if there are multiple instances of a mode (multimodal) the first instance will be returned.

• amber
• red
• green
• blue

That sample will return #N/A since there is no mode.

• amber
• red
• amber
• red

That sample will return amber because it is the first mode.

[Shurod]

I had the same base question as MMcQ, and used your formula below to solve the first part of the problem. I want the most common occuring text in A1, and the number of times that text occurs in the range in A2. I have the required formula for A2 =COUNTIF(C1:C16,A1), but I don't want the formula in A1 to count or return blanks. For example, for the range below, I want the cell to return the value "RED", not " "

• C1. AMBER
• C2. RED
• C3.
• C4.
• C5.
• C6. RED

The third part of my problem is that I want another cell to return the SECOND most common text value, so the above range would result in the word "RED" in A1 and "AMBER" in A2 with their corresponding values in B1 and B2.