Formula to count of items in a multi dropdown list
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
Best Answer
-
Paul Newcome ✭✭✭✭✭✭
一个nswers
-
一个ndrée Starå ✭✭✭✭✭✭Hi Luis,
I'd recommend using CONTAINS.
Did it work?
Hope that helps!
有一个很好的一天!
Best,
一个ndrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
一个ndrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
-
lcamacho19911 ✭✭✭
Wow, my friend, you are my hero today! Thanksssssss
-
lcamacho19911 ✭✭✭
Thanks so much
-
Paul Newcome ✭✭✭✭✭✭
-
一个ndrée Starå ✭✭✭✭✭✭
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
一个ndrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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), ""))
.
一个dd 1
=LEN([Column Name]@row)- LEN(SUBSTITUTE([Column Name]@row, CHAR(10), "")) + 1
.
一个nd there you have it.
-
Fantastic! You just allowed me to deleted about 10 helper columns!
-
Paul Newcome ✭✭✭✭✭✭
-
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)
-
一个ndré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,
一个ndrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
一个ndrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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!!
-
一个ndré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,
一个ndrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
一个ndrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/59196/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
