Percentage of certain cells in a column

Y. Czerski
edited 12/09/19 in Formulas and Functions

Probably it's easier than it looks to me, but I'm very new to Smartsheet and any help is highly appreciated. 

I have a column that contains name of several cities corresponding to complete projects in another column. I'm struggling to put together a formula that will calculate percentage of each city in the whole list. Any help of hint will be useful. Thank you in advance! 

Снимок экрана 2018-01-24 в 8.13.49.png

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/24/18

    You should use a column reference, a count function, and a countif function.

    A column reference is Columnname:Columnname

    In this case it would be City:City

     

    This references the entire column. So to count the entire city column it would be:

    =count(city:city)

     

    In order to get the % you need to divide the city name by the total. So it would be

     

    =countif(city:city,"whichever city you pick")/count(city:city)

  • Perfect, works like a charm! Thanks a lot for your help. 

     

    I'm probably asking for too much, but based on that is it possible to filter out some of the cities? If I understood right you can't use filters in formulas. What I'm trying to do is to have percentage statistics by cities but breaking them into three categories e.g. Vancouver, North Vancouver, Other. Where Other includes everything that is not Vancouver or North Vancouver. Taking into account that other cities don't include word "Vancouver" in them I thought to filter it out by that word but can't really figure out how to do it. Sorry I'm very new to Smartsheet and even after going through all possible available documentation can't figure some thing out. Any help is much appreciated. 

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/24/18

    Not a problem. What you are talking about is definitely possible, but is a bit more complicated. 

     

    =COUNTIF(City:City, FIND("Vancouver", @cell) > 0)

    The find function looks for the word in the cell and returns the location as a number. The @cell references the current value in the countif function. If the find function does not find the value, it returns a 0. Because I have the limiter >0, the formula gets rid of all null values, and counts how many values of the word Vancouver appear. Simply divide this by your count function to get a percent. To search for different text change Vancouver to whatever you wish to search for.

  • L_123
    L_123 ✭✭✭✭✭✭

    BTW this is case sensitive.

  • L_123
    L_123 ✭✭✭✭✭✭

    Also, because you wish to separate two values with Vancouver in them, use the same formula to count the number of North Vancouvers there are, and subtract that from the formula with vancouver to get the number of vancouvers there are.

  • Once again thanks for your help and a very detailed answer, which sure will help me in the future in many cases! 

    Probably you are already got tired of me, but I'm also trying to understand how to adopt this formula so that I can get a percentage of cells that DON"T contain that find word. In other words I would like this percentage info to become a part of a Sight were we can see percentage by the city. Using your first formula I can calculate percentage for Vancouver, North Vancouver, West Vancouver and everything else should fall under Other. As I now know using your second formula these 3 cities containing word "Vancouver" represent 80%, but is there a way to actually count percentage of the cells not containing find word? 

    I do apologize for being so annoying and I do appreciate all your help and can't be thankful enough. 

  • Sorry, totally missed your further replies while were typing my reply to you, but subtracting is the perfect solution for my last question to you. Thanks a lot once again for all your help! Totally made my day. 

  • Kamil Ismail
    Kamil Ismail ✭✭✭✭

    Hi Luke,



    I have one question. How to use "COUNTIF" to calculate how many user has select that one particular city.

    I have create one example but my sheet dont show the right answer:

    =COUNTIF([City]:[City], "North Vancouver")



    Really need your help.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Kamil,

    Try this.

    (You only need brackets when it's more than one word and when there are numbers in the column name)

    =COUNTIF(City:City; "North Vancouver")    

    The same version but with the below changes for your and others convenience.  

    =COUNTIF(City:City, "North Vancouver")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    I hope this helps you!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • L_123
    L_123 ✭✭✭✭✭✭

    You would do it the same way, except you would change the find() > 0 to find()=0

     

    This is because find will return a 0 if the value isn't found.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!