Creating a COUNTIFS Formula within Multi-Select drop down column to get a % figure

Stuart Richmond

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

John Jonassen

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.

Stuart Richmond

A.png

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?

David Joyeuse

Hi @Stuart Richmond

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!

John Jonassen

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.

Stuart Richmond

Thank you both for your help with this, much appreciated!