Counting number of values from one multi-dropdown to another

Chris Andrews

Hi All,

Hoping someone can help me with a COUNT formula please. I have 2 multi-dropdown columns and I'm wanting to count the number of selections from one column that appear in the other.

To give a bit of context, Column 1 could have "A", "B" and "C" (but potentially many options), and Column 2 could have "A" and "B". I would like a third column to display 2, as 2 of the values from Column 1 are found in Column 2.

Essentially i'm trying to do the below to achieve the skill %, but i'm struggling with the initial part of the formula

Is this possible?

Chris Andrews

Paul Newcome

What is the maximum number of selections possible in the "Current" column?

Mike TV

@Chris Andrews

Try this:

=COUNTM([Current Skills]@row) / COUNTM([Key Skill Requirements]@row)

This just counts the # of elements in each multiselect cell and does the math. This won't worry about human error if someone puts a skill in the Current Skill cell that wasn't one of the Key Skill Requirements.

Paul Newcome

@Mike TV I had thought about that too, but that only compares the number of selections as opposed to the actual selections. The original post leads me to believe that it is possible to have a "Current" that isn't listed in the "Key".

Chris Andrews

Thanks Both.

In terms of maximum selections, likely ~20 initially but i'd like it to be futureproof for expansion. It will be used as an internal skill comparison between an applicant and a role. So the Key Skill Requirements for a role could be "Smartsheet", "Leadership" and "Problem Solving", and the Current Skills could be "Excel", "Leadership", "Project Management" and "Problem Solving". I'd want to show that this would be a 66% match, so returning 2 from the first part of the formula as "Leadership" and "Problem Solving" are found in both columns.

Just looking for something to find common selections between the two.

Paul Newcome

So it is possible to have something listed in "Current" that is not listed in "Key", and we need something that can easily scale.

What about the maximum number in "Key"?

Chris Andrews

Yes, exactly. "Current" and "Key" will both contain multiple values, but each could contain values that are not present in the other. I'm looking for a count of values that appear in both. Think skills required for vacant roles in the business vs skills held by the applicants in the pool

On scalability, the higher the better but realistically I'd like to keep to a selection of <20 in each, and could look for both columns to pull from the same data set of values if needed, again with a higher value as possible.

My initial thought was to compose an essay full of countifs and contains but even if it works that could become unwieldy so I was hoping there was something available to just look at selected values, almost like a fancy compare function. Count of the numbers of values selected that appear in both cells

Paul Newcome

Unfortunately there is no "easy" way to do it. We are basically going to have to parse out one of them and pull our counts from there.

You could do a series of IF/CONTAINS and add them all together, but as you said... That gets pretty unwieldy pretty fast.

Here's my suggestion:

Insert a text/number column called "Start" and a text/number column called "End". These are going to both be left blank.

Then insert a checkbox column in between. Lets just call this first one Smartsheet. Enter this column formula:

=IF(HAS([Current Skills]@row, "Smartsheet"), 1)

Then insert more checkbox columns for each of the different KEY skills (make sure they go IN BETWEEN the two blank columns). It is going to take a fair amount to set up, but after it is set up you should only need a minute or two to add a new skill.

Finally you can get your percentage by adding up how many boxes are checked and dividing by how many selections were made in the Key column.

=COUNTIFS(Start@row:End@row, @cell = 1) / COUNTM([Key Skill Requirements]@row)

The two blank column flanking the checkbox columns help with scaling. Since we are referencing those two columns in out COUNTIFS, any columns inserted between them will automatically be included in the count. So to add a new Key skill, you would insert a new checkbox column between the two blank ones, then copy/paste the formula from one of the other checkbox columns and tweak it to search for the new Key skill.

I know it is not pretty, but you can hide the checkbox and two blank columns until you need to add a new one to keep the sheet clean. It also makes scaling a little more manageable since you won't have to keep adding to a larger and larger formula hoping that you don't accidentally misplace a parenthesis. Just unhide the columns, insert a new column, copy/paste/tweak a simple formula, then hide the columns again.