Count of Dropdown (multi-select) items in another sheet

Frank Falco

I have a sheet that has a column called Steps, Values Step 1, Step 2,...Step 10

From another sheet, I want to count how many Steps happen in a Stage (which contains a number of Steps)

I would like a count formula that looks at the Steps in each Stage and counts how many rows in the other sheet have those Steps in it. I need to be able to edit the table that assigns the Steps to the Stage.

I realise that I could hard code a number of countifs for each step but I would like to have it user editable as a table above.

So for example, Stage 1 would be a count how many rows have either Step 1 or Step 2 in the Step column on the related sheet.

Paul Newcome

How many possible steps could there be for a stage? The most straightforward way of accomplishing this would be to do what you are trying to avoid and hard-code a COUNTIFS for each step. Another option would be to use helper columns and parse the steps out across the row and then use cell references instead of hard-coding the steps.

Frank Falco

It varies and could be any number. I was hoping for an elegant solution but it looks like hard coding is the way to go.

Multi-select is a little half baked and can be difficult to deal with. It would be useful to be able to address each element or as in this case use the elements as a virtual or condition in a formula.

I will try and describe this as a Product Enhancement request.

Thanks Paul

Paul Newcome

The hardest part isn't so much the multi-select in and of itself (in my opinion). I personally think the challenge here is using individual portions of a text string.

It would be helpful if we could individually reference each selection. Right now the delimiter is CHAR(10) which is a line break. If Smartsheet can leverage that into a new function to be able to specify, that would be amazing.

Lets just say the new function is "MULTI". The way I'd like to see it work would be something along the lines of...

=MULTI(range, selection number)

Where range would be your range whether it be a group of cells or a single cell reference and the selection number would be where you put a number to say which selection you want.

So using the MULTI function in a COUNTIFS could look something like this...

=COUNTIFS([Other Column]:[Other Column], OR(CONTAINS(MULTI(Step@row, 1), @cell), CONTAINS(MULTI(Step@row, 2), @cell)))

Granted using this method you would still need to draw out each of your Steps within the OR function, but you wouldn't have to hard-code anything.

I guess you could say it would work similar to a MID statement, but instead of having to specify a starting point and the number of characters, it automatically calculates those on the back-end based on the positioning of the line break.

This would also be helpful when trying to parse out any data in general. Some solutions I have built use a JOIN function with a unique delimiter and then you have to go in with a series of formulas to parse the data back out (for example on a different sheet), whereas with the MULTI function I could make my JOIN delimiter CHAR(10), and then just use the MULTI function.

As I typed out that last bit...

Maybe instead of a MULTI function we could have a PARSE function where we can specify the delimiter to search for.

=PARSE(range, selection number, delimiter)

I think I may submit an enhancement request for that one myself... In fact, I have gone ahead and started a new discussion surround a PARSE function HERE.

Rafael Munhoz

Have you tried using COUNTM?

Paul Newcome

@Rafael Munhoz How would you incorporate a COUNTM function?

ABHI

You can use:

Countifs({column_in_anothersheet},FIND("Dropdownlist_word", @cell) > 0)

This will give you the count in that column.