Excel: How do I convert multiple row entries in a column as a percentage of the total entries in another cell?

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.

-

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`

-

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.

-
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