How to count the number of occurrences of all cell values in a specified range

I have an Excel worksheet with one column of cells with any string values, so, like this: A, B, A, D, C, C, A, A. What I am wanting to do is to count how many times each value occurs and print that out on some other cells, like: A:4, B:1, C:2, D:1. I don't mind if these are spread across 2 columns. I also want to order these, with the most frequent first, like: A:4, C:2, B:1, D:1. How should I go about doing this?

Edit: I also do not know what values will occur.

microsoft-excel-2003

James Wood

Posted 2010-10-18T22:39:33.213

Reputation: 153

To clarify. Will each cell only have a single value? A or B or C or possibly multiple values in a cell such as A,B,C? Countif will not work for that situation. Also you mention not knowing what values will occur. Do you mean there are other possibilities besides A, B,C,D and your solution needs to allow for a random X or Q etc wwhich may not have been anticipated in advance? – datatoo – 2010-10-22T16:00:52.507

Answers

You can also use a pivot table for this if you put a title in the cell above your list and then pivot (either in row or column) on your title cell and use count on your title field in the body (data) of the pivot table.

Lord Peter

Posted 2010-10-18T22:39:33.213

Reputation: 403

@James Wood - you can associate your accounts on superuser and stackoverflow. Not sure about Facebook but I use Google OpenID for mine and it works v well. Excel pivot tables are great, and I'm always learning new things to use them for! – Lord Peter – 2010-10-23T17:39:23.623

In Excel you would use the "countif" function "=countif(A1:A9,"a") where A1:A9 is the range you want to count and "a" is the item you want to count.

You can also use the Insert>Function form the tool bar to insert functions. This will bring up a dialog box so you can pick the function. Once you pick the function, another dialog box will open that will allow you to enter in your parameters. This box will also show the results once the parameters are filled in. There is also a "Help on this Function" link on the bottom of the box.

To sort the results, Type in the item you are counting into the cells adjacent to the formulas (for example "A"). Then use Data>Sort from the tool menu.

If you do sort them, then range(s) will need to set to constant by adding a "$" in front of each argument of the range as in this example "=COUNTIF($A$1:$A$9,"a")". Another way is to name the range of values by: highlighting the range and use Insert>Name>Define and then type in the name you want to use "Input" for example. Then the function would look like this "=COUNTIF(Input,"a")"

RSMoser

Posted 2010-10-18T22:39:33.213

Reputation: 537

+1 for pointing out the insert function feature and how it can assist in creating a formula – datatoo – 2010-10-22T15:42:54.427

There are a few tools that will help - primarily creating 'COUNTIF' fields which are then sorted will give you what you are after

I use Open Office as a base so I am unable to give a direct example

The '=COUNTIF(A1:A10;2006)' command allows you to specify the range then provide a value, if any cell equals the value, it will be counted.

dpmguise

Posted 2010-10-18T22:39:33.213

Reputation: 101