Counting Criteria that Overlaps
I have a multi select dropdown column where users can select phases of a job they have experience in. For this example, let's say they have the options of "Pre-Execution", "Execution", and "Closeout". I want to total the number of users with "Execution" experience.
Using "=COUNTIF([dropdown]:[dropdown], CONTAINS("Execution", @cell))" includes both "Pre-Execution" and "Execution" in the user count, thus if the user only has "Pre-Execution" experience then they incorrectly get counted. Replacing CONTAINS with HAS then counts users with ONLY "Execution" experience, but if they also have other experience it excludes them completely. Same problem with using a NOT variation as I don't want to exclude those with both "Pre-Execution" and "Execution" experience. Any help on the correct formula to use?
Best Answer
-
After taking a mental break, here's the formula I eventually came up with to solve the issue:
=COUNTIFS(dropdown:dropdown, CONTAINS("Pre-", @cell), dropdown:dropdown, SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") > 1) + COUNTIFS(dropdown:dropdown, CONTAINS("Execution", @cell), dropdown:dropdown, NOT(CONTAINS("Pre-", @cell)))
Breaking this down, I use SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") to count how many times "Execution" exists in a cell. Using a COUNTIFS, I count the cell if it has both "Pre-" AND more than one instance of "Execution" (hence the ">1"). This covers the cases where "Pre-Execution" and "Execution" occur simultaneously. In instances where only "Execution" occurs without "Pre-Execution", the second COUNTIFS in the formula counts it by looking for "Execution" without an instance of "Pre-". Hope this helps future users!
Answers
-
Paul Newcome ✭✭✭✭✭✭Try something like this...
=COUNTIF([dropdown]:[dropdown],AND(CONTAINS("Execution", @cell), NOT(CONTAINS("Pre", @cell))))
This will count the cells that do contain "Execution" and do not contain "Pre".
-
Paul,
I appreciate the help. Unfortunately, that excludes the users that havebothPre-Execution and Execution experience so I get an incorrect count of total users that have "Execution" experience.
-
Paul Newcome ✭✭✭✭✭✭
Good point.
Would it be possible to maybe add an "s" onto the end of one of them such as "Executions" or "Pre-Executions" (but not on both)?
-
Yes, I've thought of doing a modification along those lines as well but was seeing if I could avoid it for a smoother user interface (as each employee fills out a form to build the experience database), though it's not that big of a deal so can go that route if needed.
Any idea on how smartsheet's internal column filters/report filters work? There you can select the "Execution" filter checkbox and it works - thinking out loud here.
Also, you won my self bet on if you or Andres would get to my question first - congrats! haha
-
Paul Newcome ✭✭✭✭✭✭
I haven't been able to figure anything else out aside from parsing each selection into its own column.
Although... If they in a multi-select dropdown, then the order would always be the same. I wonder if there might be a way to leverage the delimiter or us a FIND function... I am going to do some testing and revisit this thread.
Hahaha. I am fairly certain@Andrée Staråand I are in different time zones. He is usually on earlier than I am, and I tend to get more of the later posts it seems like.
-
Paul Newcome ✭✭✭✭✭✭
I think I may have a solution using the FIND function, but I need to figure out a specific little detail to be able to accommodate everything... It looks like the FIND function reads your selections in alphabetical order even if they do not appear that way. For example...
In your column properties you list out D, C, B, and A as your options in that order. When a user selects multiple options within the same cell, they are populated in the same order as the column properties. However, using a FIND function to search for "A" will always produce a 1 or a 0. If "A" was not selected, then you will get a 0, but if it was selected (with or without additional selections being made), the FIND function will produce a 1 even though it is displayed last based on your column properties.
All of that is new info to me, so I am going to have to play around with it a little bit to figure out how exactly to make it work. I think it is going to depend on your exact options though, so were you using "Pre-Execution", "Execution", and "Completion" as examples, or are you actually using them in your sheet? Are there any other selections? Can you provide a comprehensive list that includes every selection you plan to make available?
@Andrée Starå@Genevieve P@Mike Wilday@L@123Did y'all know about the above (Re: FIND function's output when looking at a Multi-Select column)?
-
Mike Wilday ✭✭✭✭✭✭@Paul NewcomeThat is really interesting. Thanks for sharing. I will have to play around with that as well!
-
Interesting. You are correct, I was using those three as examples for this issue. In my actual sheet, this item appears in several different columns as it's for different project types. The options also sometimes vary slightly depending on the project type. However, I'm savvy enough with programming languages that if you work it out for the example I'll be able to modify and apply over to my sheet with no problem.
-
After taking a mental break, here's the formula I eventually came up with to solve the issue:
=COUNTIFS(dropdown:dropdown, CONTAINS("Pre-", @cell), dropdown:dropdown, SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") > 1) + COUNTIFS(dropdown:dropdown, CONTAINS("Execution", @cell), dropdown:dropdown, NOT(CONTAINS("Pre-", @cell)))
Breaking this down, I use SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") to count how many times "Execution" exists in a cell. Using a COUNTIFS, I count the cell if it has both "Pre-" AND more than one instance of "Execution" (hence the ">1"). This covers the cases where "Pre-Execution" and "Execution" occur simultaneously. In instances where only "Execution" occurs without "Pre-Execution", the second COUNTIFS in the formula counts it by looking for "Execution" without an instance of "Pre-". Hope this helps future users!
-
Genevieve P. Employee AdminPaul - that is really interesting. I wasn't aware that FIND read things alphabetically like this, great discovery!
-
Paul Newcome ✭✭✭✭✭✭
@Alan DonovanNicely done. Glad you were able to get it working, but I think I am still going to play around with the FIND function out of my own curiosity. Haha.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+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-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
