Jul 18 2018
11:40 AM
- last edited on
Jul 31 2018
08:46 AM
by
TechCommunityAP
Jul 18 2018
11:40 AM
- last edited on
Jul 31 2018
08:46 AM
by
TechCommunityAP
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%.
Jul 18 2018 11:51 AM
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"))
Jul 18 2018 02:18 PM
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")))