Creating a COUNTIFS Formula within Multi-Select drop down column to get a % figure
Hi,
I've created a tracker that captures information on the make ups of Boards.
One column is called 'Diversity' and has the option to multi select either "BAME" or "Disability"
What i'd like to be able to do is to Count the number of times either BAME or Disability is chosen and then divide that by the number of entries to get a percentage.
This will then say X Board has x% of its Board that has either BAME or Disability members on it.
I'm currently using the formula:
=COUNTIFS(Diversity:Diversity, CONTAINS("BAME", @cell)) + COUNTIFS(Diversity:Diversity, CONTAINS("Disability", @cell)) / COUNTM(Diversity4:Diversity15)
This does count the number of times BAME or Disability appear, but i can't turn that number into a %.
On other columns, with similar formulas, i can turn the number into a %.
Can anyone help?
Thank you in advance
Answers
-
Try this, @Stuart Richmond
=COUNTIFS(Diversity:Diversity, OR(CONTAINS("BAME", @cell),CONTAINS("Disability", @cell))/ COUNT(Diversity:Diversity)
By adding the countifs of both BAME and Disability together, you could potentially end up with a percentage greater than 100% as your multi select could have both options in one, thus counting a Diversity entry twice rather than once.
-
Hi John,
Thank you for taking the time to respond to my query.
I tried to use your solution, but it came up with an 'Invalid Operation'.
I've attached an image of the original formula i was using.
In the Diversity Column, using this formula, there are 2 cells out of 12 that had 'Disability' or 'BAME', and the cell shows '2'.
What I want the formula to do is to turn the '2' into a % figure - so, for this example, 2 out of 12 would be:
2/12*100 = 16%
Does that make sense?
-
I think your first formula is correct if it's a multi-select dropdown column, you'd better use COUNTM instead of COUNT that will only counts cells that are not blank.
Still I think your formula is flawed because of operations priority. Either put parenthesis around your sum of COUNTIF, or use the Function SUM. Because right now, the "/" is treated prior to the "+". Finally, your ringe in the COUNTIFS is not the same as in the COUNTM , they have to be equal to have a correct calculation.
=(COUNTIFS(Diversity:Diversity, CONTAINS("BAME", @cell)) + COUNTIFS(Diversity:Diversity, CONTAINS("Disability", @cell))) / COUNTM(Diversity:Diversity)
Within a % column you should have what you're looking for.
The invalid operation from @John Jonassen is because of a lacking closing parenthesis:
It lacks a last parenthesis prior to the "/" to close the COUNTIFS function.
=COUNTIFS(Diversity:Diversity, OR(CONTAINS("BAME", @cell),CONTAINS("Disability", @cell)))/ COUNTM(Diversity:Diversity)
It should be working fine as well.
Concerning what you really want I'd rather go with the SUM of COUNTIFS but it really depend on how you want Smartsheet to count cells where there is both "BAME" and "Disability". If you want it to count this as 2 value, then use the SUM of COUNTIFS and if you want to count it as 1, then the OR will be good.
Hope it helped!
-
COUNTM will count each selection in a multi-select as 1, so if A and B are selected in the Diversity column, then the count will be 2 rather than the 1 that you are expecting. COUNT does count only those that are not null.
-
Thank you both for your help with this, much appreciated!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!