Simple Excel Function

[Spooner]

If I have a column with a bunch of cells that contain various text data and I want to get a count of how many of each there are, what function do I use?

Example:

A1........................A2
Beach...................Beach 3
Beach...................Car 2
Car.......................Banana 2
Banana
Beach
Banana
Car

 

[isasir]

=COUNTIF(A1:A300,"Beach")

etc.

 

[Spooner]

but what if i don't know how many different items are in the column?

 

[isasir]

I don't get what you mean. A1:A300 can vary depending on how many rows column A takes up. "Beach" was used since it was one of your choices. Do you not know what are the potential items that'll be listed in column A?

 

[Spooner]

Do you not know what are the potential items that'll be listed in column A?

Exactly. I'd like excel to look down the column, and provide a count of how many different items there are without me specifying what exactly it's counting. does that make sense?

 

[Turkish]

if you know a little programming, you can do that through VBA.
View > toolbars > visual basic.
i dont have time right now but search for lookup functions.

 

[Spooner]

i don't know any vba

 

[isasir]

Originally posted by: Spooner

Do you not know what are the potential items that'll be listed in column A?

Exactly. I'd like excel to look down the column, and provide a count of how many different items there are without me specifying what exactly it's counting. does that make sense?

Makes sense, but I don't think I can help you out with that. I guess someone with VB knowledge can though.

 

[Spooner]

I thought this would be easy..... hmmmmm.....

 

[robphelan]

Originally posted by: Spooner

Do you not know what are the potential items that'll be listed in column A?

Exactly. I'd like excel to look down the column, and provide a count of how many different items there are without me specifying what exactly it's counting. does that make sense?

you need to get Unique records. Do this by:
1) sort the column,
2) select the entire column by clicking on the column header.
3) Click Data -> Filter -> Advanced Filter
4) choose 'Copy to another location'
5) Check 'Unique Records Only'
6) in the 'Copy To' Box, type in $D1 (or wherever you want to place your unique records)
7) click OK

that should do it.

 

[z0mb13]

use countif

countif(range to check, "beach")

not so sure about the syntax.. go look up excel help

 

[cronos]

or you can import to Access and do a:
select a1, count(a1) group by a1;
or something like that

 

[isasir]

Originally posted by: robphelan

Originally posted by: Spooner

Do you not know what are the potential items that'll be listed in column A?

Exactly. I'd like excel to look down the column, and provide a count of how many different items there are without me specifying what exactly it's counting. does that make sense?

you need to get Unique records. Do this by:
1) sort the column,
2) select the entire column by clicking on the column header.
3) Click Data -> Filter -> Advanced Filter
4) choose 'Copy to another location'
5) Check 'Unique Records Only'
6) in the 'Copy To' Box, type in $D1 (or wherever you want to place your unique records)
7) click OK

that should do it.

Hmm, I guess you'd still need to combine this with a countif to get a count of each item, but at least now you'd know the various items in the column?

 

[z0mb13]

countif is the answer!!! it is very easy to use! just do multiple countifs for each item u want to check

 

[Turkish]

i am trying to find somethign on google for you... if you really need to this and are in a hurry, i suggest Experts-Exchange.com.

 

[minendo]

Use the vlookup function coupled with countif.

 

[Spooner]

i took rob's example and did the unique values in a new column, then simply looked at the cell number for a quick count!!!!

Thanks all!!!

 

[NeoV]

I have a few alternatives....

A pivot table would actually be the best way to summarize everything, but if you don't know how to use that I'll leave that one alone...

you can figure out the bottom row of each column, assuming each row has something in it until the end, with =65536 - countif(a1:a65536,"")