Percentage Formula - Text to %

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

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…