Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count Instances
How can I count the number of times each of numerous
items appears in a column? That is, I have a column a a b b b c c c c d d etc. Each entry represented by a letter above is in fact several words. There are about 10,000 rows in the table. I would like result like a 2 b 3 c 4 d 2 etc. Thanks in advance. Ken |
#2
|
|||
|
|||
You can use the countif function, say for example you wanted to find the
number of occurances of the letter a in the column (which is column A) =COUNTIF(A1:A10000,"a") If you have many entries you could have a table of the occurances you are looking for and instead of "a" you reference that cell. Alternatively you could create a Pivot Table, Data PivotTable and PivotChart Report. Select Excel list, select the data range, click layout, from the fields on the right drag the column heading to the left side of the table, and then drag to the middle and double click that field and click count. Click OK and decide where you want the table. The result will be table giving you a count of all the unique occurances. To update the table as you add data simply right click, refresh data. Make sure that your column are properly labelled. Regards "Ken" wrote: How can I count the number of times each of numerous items appears in a column? That is, I have a column a a b b b c c c c d d etc. Each entry represented by a letter above is in fact several words. There are about 10,000 rows in the table. I would like result like a 2 b 3 c 4 d 2 etc. Thanks in advance. Ken |
#3
|
|||
|
|||
If your data is sorted (and has headers), you could use Data|subtotals and hide
the detail rows. But data|pivottable is very nice for this. If you want to read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Ken wrote: How can I count the number of times each of numerous items appears in a column? That is, I have a column a a b b b c c c c d d etc. Each entry represented by a letter above is in fact several words. There are about 10,000 rows in the table. I would like result like a 2 b 3 c 4 d 2 etc. Thanks in advance. Ken -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Count of Records Retrieved. | Excel Discussion (Misc queries) | |||
Count number of instances in sheet e.g. how many males/females | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions | |||
count based on two fields - need quickly | Excel Worksheet Functions |