SOLVED

Excel Function or Formula

Copper Contributor

Trying to get a percentage of a text from a column... Tried COUNTIF(B2:600,"B")/COUNTA(B2:B600), but it's not accurate. 

11 Replies

@BR0_5 

That's percentage of cells with values as letter B. What do you mean under "not accurate" ?

Yes percentage of cells with letter B. When I used the formula above the outcome is not correct.

@BR0_5 

What means not correct? That's 78.5999999% instead of 78.6% or that's 10% instead of 90% ?

Why don't you provide some sample like

image.png

@Sergei Baklan 

 

100% is not correct, please see below:

BR0_5_0-1615589234870.png

 

 

best response confirmed by BR0_5 (Copper Contributor)
Solution
That is correct. If you want to divide by total number of rows and not the number of rows that have text in them you can use =COUNTIF(A1:A13,"B")/ROWS(A1:A13)

@BR0_5 

Another variant could be

=COUNTIF(A1:A10063,"b")/(COUNTA(A1:A10063)+COUNTBLANK(A1:A10063))
What if I add 2 or more text values in column A such as “c” and “nr”? Would you recommend a different formula?
you can try this: =COUNTIF(A1:A100,"> ")/ROWS(A1:A100)
What if the number of rows keeps changing?
what is the number of rows based on? If you want to include blank cells in the denominator you have to give us something to go on. Is there another column that isn't blank that you can use to count the total number of rows to use?
The number of rows based on # of shots and “b” is made shots. I could use another column that isn’t blank to count the number of total rows...
1 best response

Accepted Solutions
best response confirmed by BR0_5 (Copper Contributor)
Solution
That is correct. If you want to divide by total number of rows and not the number of rows that have text in them you can use =COUNTIF(A1:A13,"B")/ROWS(A1:A13)

View solution in original post