Count appearances of text value in column

**connor8392** · 04-28-2010, 04:54 PM

What function would I use to count how many times a text value has appeared in a column, for example if I wanted to find how many times "Las Vegas" appeared in a column.

However, I would like to find the most common text value in a column and create a graph from this showing which is the most common text value, this would be done on another sheet.

How would I go about doing this?

Thank you for your time,
Connor

**Shampoo Monkey** · 04-28-2010, 05:54 PM

Hey Connor,

You would want to use the Countif function, which would look like this:

=COUNTIF(A:A,"Las Vegas")

The other might be a little more complicated. Do you have a complete list of the different text values? ie Las Vegas, Portland, Sacramento etc

Does this list change?

**sailepaty** · 04-28-2010, 07:31 PM

To get the most common text in a range use this,

=INDEX(A1:A100,MATCH(TRUE,COUNTIF(A1:A100,A1:A100)=MAX(COUNTIF(A1:A100,A1:A100)),0))

Confirm with Ctrl+Shift+Enter

Regards

**connor8392** · 04-29-2010, 02:45 AM

Thanks, thats working as it should now

Do you know how I could make a graph for the most visited destination?

Thanks for your time,
Connor

**DonkeyOte** · 04-29-2010, 03:05 AM

Originally Posted by connonr8392

Do you know how I could make a graph for the most visited destination?

Use a Pivot Chart ... in terms of config. on the underlying Pivot Table itself your "Column A" would be both Row Field and Data Field (set to Count).
(see link in my sig. for general intro if unfamiliar)

On a formula note: for finding most frequent text I'd perhaps suggest an alternative:

=INDEX(A1:A100,MODE(MATCH(A1:A100,A1:A100,0)))

though conceded, unlike the prior suggestion the above would expect one word to appear at least twice.