Percentage of certain cells in a column

Y. Czerski

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! 

L_123

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)

Y. Czerski

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

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

BTW this is case sensitive.

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.

Y. Czerski

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. 

Y. Czerski

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

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å

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

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.