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

Question

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.

Answer 1

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

Answer 2

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.