Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Example: Under column B

row 10: mop
row 11: top
row 12: cop
row 13: mop

In another cell in another column, I'd like to know the percentage of the word "mop" (50%) and another cell would be the percentage of the occurrence of "top" (25%), etc. More rows will be added as needed and the data in column B does not start until row 10. Thanks.

share|improve this question
up vote 2 down vote accepted

Assuming the words are in column B and start at row 10 and you want to be able to fill the sheet to row 65536 (the maximum):

To determine the percentage for "mop":

=COUNTIF($B$10:$B$65536,"mop")/COUNTA($B$10:$B$65536)

For "top":

=COUNTIF($B$10:$B$65536,"top")/COUNTA($B$10:$B$65536)

And so on.

If there is nothing in column B in rows 1-9 then you can just use $B:$B instead of $B$10:$B$65536

share|improve this answer

Copy and paste the list using an advanced filter to copy just the distinct values. Copy these values to column B.

In column C create a formula that uses COUNTIF that looks a the the A column values and compares it to the value in the same row B column. Something like: =COUNTIF(A$10:A$13,C10)

Somewhere else use COUNTA or a similar function to count the number of rows in column A with a value.

In column D create a formula that divides the column C count for each word by the total count value to give you a percentage.

share|improve this answer
    
The answer given does not account for column A changing by entries being added. I want it to be dynamic. – MattUebel Mar 2 '11 at 1:19
    
Add a larger range than A$13, you could go to the max if you wanted. – jzd Mar 2 '11 at 3:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .