Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the number of times a cell value is within a specific range Everett Excel Worksheet Functions 4 September 2nd 06 11:54 PM
how do i count text and display it as text plus the # times it hap Count in Excel New Users to Excel 1 April 26th 06 11:15 AM
How to count the number of times something occurs within a certain month Joyce Excel Worksheet Functions 2 October 18th 05 09:38 PM
how to count the number of text frequencies and copy to other cell DG Excel Worksheet Functions 1 October 6th 05 08:11 PM
count the number of times the same number shown Noemi Excel Discussion (Misc queries) 1 September 22nd 05 05:00 AM


All times are GMT +1. The time now is 10:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"