Formula to count of items in a multi dropdown list

lcamacho19911

Hi

The drop-down multi select is very nice and useful.  I started to use it yesterday, but I need to use the formula =countif(range, criterion). 

I want to count how many times a value is repeated in a range that has multi values. I use the formula countif and only count one time a repeated value when the cell have multiple values.  I hope I explain well my point.

Luis from PR

Paul Newcome

=COUNTFS(range, CONTAINS("specific text", @cell))

Andrée Starå

Hi Luis,

I'd recommend using CONTAINS.

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

=COUNTFS(range, CONTAINS("specific text", @cell))

lcamacho19911

Wow, my friend, you are my hero today! Thanksssssss

lcamacho19911

Thanks so much

Paul Newcome

Happy to help. I was just giving an example of Andree's solution. 

Andrée Starå

Happy to help!

Caitlin Gaines

I'm trying to count the total number of selections that were made in a multi-select column, regardless of the selection made.

Ex. Multi-select options are A, B, C, D

Row 1: Selected B = 1

Row 2: Selected A, C, D = 3

etc.

How would I calculate this?

Paul Newcome

The first thing you need to know is the delimiter that SS uses. It is a line break which is CHAR(10).

 

From there we can take the total number of characters in the cell and subtract from that the number of characters once the delimiter is removed. Adding 1 to that result will give you how many selections were made.

.

Number of characters in the cell

 

=LEN([Column Name]@row)

.

Removing the delimiter

SUBSTITUTE([Column Name]@row, CHAR(10), "")

.

Number of characters without delimiter

LEN(SUBSTITUTE([Column Name]@row, CHAR(10), ""))

.

Subtract the second number from the first

=LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), ""))

.

Add 1

=LEN([Column Name]@row) - LEN(SUBSTITUTE([Column Name]@row, CHAR(10), "")) + 1

.

And there you have it.

Caitlin Gaines

Fantastic! You just allowed me to deleted about 10 helper columns!

Paul Newcome

Haha. That's always a good day. Happy to help! 

markkrebs

Added an isblank to accommodate any rows with nothing selected

 

=IF(ISBLANK([Items completed]@row), 0, LEN([Items completed]@row) - LEN(SUBSTITUTE([Items completed]@row, CHAR(10), "")) + 1)

Paul Newcome

They have recently (a few weeks ago) actually come out with a new function that replaces the LEN - LEN/SUBSTITUTE.

 

=COUNTM([Multi-Select Column]@row)

Andrée Starå

Hi Mark,

Here's some more info about the new functions.

https://community.smartsheet.com/discussion/two-new-functions-released-countm-has

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

ademoss

Hi guys! I have a similar situation and I'm trying to use the formulas above. I've got dropdowns for a column where I'm indicating the TYPE of task I'm doing, and I want to be able to count cells that include two specific types of task. I have five or so options, but want to count which ones are P&C AND Valuations. 

So far I'm trying AND functions to no avail; =COUNTIF(Type:Type, (CONTAINS "Valuations"), AND(Type:Type, (CONTAINS "P&C"))

The counting I'm doing one type at a time is fine; =COUNTIFS(Type:Type, "P&C")

I'm relatively new to this, so any help would be appreciated!! 

Andrée Starå

Hi,

Try something like this.

=COUNTIF(Type:Type; "Valuations") + COUNTIF(Type:Type; "P&C")

The same version but with the below changes for your and others convenience.

=COUNTIF(Type:Type, "Valuations") + COUNTIF(Type:Type, "P&C")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée