Formula Issues

Copper Contributor

What is the formula to divide words in a column to reach a percentage? For example, if the words are "One""Two""Three" I'm looking to divide the total amount of "One"and"Two" by the total amount of "Three" and "One". So if there were 1 "One", 1 "Two", and 16 "Three" the equation would be 2/17 and the answer would be 11.76%. 

3 Replies

The best I can think of is the following, except I am met with the DIV/0 error.

 

=COUNTIFS(H2:H100,"ONE",H2:H100,"TWO")/(COUNTIFS(H2:H100,"THREE",H2:H100,"ONE"))

Hi Jeff,

 

COUNTIFS() takes criteria with AND condition. Other words you try to calculate number of cells which simultaneously are "One" and "Three". No such.

 

Try this

=(COUNTIF(H2:H100,"ONE")+COUNTIF(H2:H100,"TWO"))/((COUNTIF(H2:H100,"Three")+COUNTIF(H2:H100,"One")))

It worked! Thanks! :)