Percentage Formula - Text to %

J.Henderson

Hi all,

I have 35 rows with multiple text option values.

In the 36th row I want to calculate the overall % of cells complete (out of 100%) for the 35 cells, deducting the blank values off the total %.

Is there a way of doing this based on text value?



Many thanks,

Kindest regards,

J Henderson

 

 

CaseyDuBose

You can do this with a count of ISBLANK in a formula. 

I made an example for you here. 

https://app.smartsheet.com/b/publish?EQBCT=e145f188abb04adea5186d11605dc5b0

Delete the text values and it'll update the percentage in the green cell. 

=COUNTIF(CountColumn1:CountColumn36, ISBLANK(@cell) = 0) / 36

This formula is a bit brittle because of your requirement to put it at the bottom of the list. If you are willing to do it as a parent row, you get the ability to have it dynamic update according to the number of input rows. 

 

 

Genevieve P.

Hello J Henderson,

Another option would be to do this with a COUNTIF formula that calculates the number of blank cells in that row, minuses that number from 35 to have your total number of cells with text, then divides that adjusted number by 35 for the percentage. In my sheet, the first column is still named "Primary Column", so my formula in the percent column looks like this:

 

=(35 - (COUNTIF([Primary Column]@row:[Column35]@row, ""))) / 35

 

This COUNTIF formula counts if a cell is blank (written as "", two quotation marks without a space), within the range of my Primary Column's cell in that row to Column 35's cell in that row. You can read more about the @row function, here.

Make sure your 36th column is set up to calculate percentages by clicking on the column name to select the whole thing, then choosing the % symbol from the toolbar at the top of your sheet.

Cheers!

Genevieve

MONICA BUENDIA

What if I am using yes or no and using the summary sheet to get a percentages in order to quantifiy several questions. The problem is I cannot get the % sign to show but just the number. I used a countif formula/count*.01 but I need the % symbol to show. Does that make sense?

Genevieve P.

Hi @MONICA BUENDIA

It looks like you've received a few answers to this question on your post, here: How can I get a percent sign on the 50 noted below that is calculating text i.e. yes or no?

If those answers haven't helped, feel free to tag me on that post and I'm happy to clarify further.

Cheers,

Genevieve