How do I create an CountIF statement to include multiple selections within a cell?

I'm creating sheet summary calculations to tally the total count of a specific dropdown menu item; however, I am using multi-select. I know how to use a standard COUNTIF formula, but it only returns the total count if the option is the only one selected. Here is the formula I'm using:

=条件统计(Level III Request]:[Level III Request], "Solution Video")

The option for Solution Video appears six times but only three times by itself, so the formula returns a total count of 3. What logic is required to count the option when bundled with other selections? Below is a screenshot of my sheet. Any help is greatly appreciated!

COUNTIF Statement Inquiry for Multi-Select Fields.png


Tags:

Best Answers

  • gstotts
    gstotts Employee
    edited 10/13/23 Answer ✓

    Try using a CONTAINS() statement in your criteria for the COUNTIF:

    =条件统计(Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey@Jeannette Leary

    Can you post the formulas you've tried?

    In any of your formulas (e.g. COUNTIFS) you'll need to make sure you're also using HAS to see if the cellhasthat value selected (versus = that exact value without other selections).

    For example:

    =COUNTIFS({column range}, HAS(@cell, "Value"))

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @JPavlasek<\/a> ,<\/p>

You can use this formula in the checkbox row.<\/p>

=IF([10\/24]@row > [10\/17]@row, 1)<\/p>

Keep in mind, this only works for 10\/24 and 10\/17, if you are trying to compare other columns, you would need to change the formula.<\/p>

Hope this helps,<\/p>

Dave<\/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":112105,"type":"question","name":"Updating Overall Status based on 3 other Statuses","excerpt":"Hello, I need help writing a formula for the Overall Health cell based on the Scope Health, Budget Health, and Schedule Health cells. If any of the 3 cells are yellow, Overall is yellow. If any of the 3 cells are red, Overall is red. If all 3 are green, overall is green.","snippet":"Hello, I need help writing a formula for the Overall Health cell based on the Scope Health, Budget Health, and Schedule Health cells. If any of the 3 cells are yellow, Overall is…","categoryID":322,"dateInserted":"2023-10-24T20:51:01+00:00","dateUpdated":null,"dateLastComment":"2023-10-24T21:38:06+00:00","insertUserID":169012,"insertUser":{"userID":169012,"name":"Bvinston","url":"https:\/\/community.smartsheet.com\/profile\/Bvinston","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T21:37:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169012,"lastUser":{"userID":169012,"name":"Bvinston","url":"https:\/\/community.smartsheet.com\/profile\/Bvinston","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T21:37:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3396365347,"url":"https:\/\/community.smartsheet.com\/discussion\/112105\/updating-overall-status-based-on-3-other-statuses","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112105\/updating-overall-status-based-on-3-other-statuses","format":"Rich","lastPost":{"discussionID":112105,"commentID":401493,"name":"Re: Updating Overall Status based on 3 other Statuses","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401493#Comment_401493","dateInserted":"2023-10-24T21:38:06+00:00","insertUserID":169012,"insertUser":{"userID":169012,"name":"Bvinston","url":"https:\/\/community.smartsheet.com\/profile\/Bvinston","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T21:37:00+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/S7WD7IY2UTRM\/capture-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/S7WD7IY2UTRM\/capture-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/S7WD7IY2UTRM\/capture-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/S7WD7IY2UTRM\/capture-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/S7WD7IY2UTRM\/capture-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/S7WD7IY2UTRM\/capture-png.png"},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-25T00:25:51+00:00","dateAnswered":"2023-10-24T21:33:27+00:00","acceptedAnswers":[{"commentID":401492,"body":"

Hi @Bvinston<\/a> <\/p>

Please see if this will work for you:<\/p>

=IF(CONTAINS(\"Red\", [Scope Health]@row:[Schedule Health]@row), \"Red\", IF(CONTAINS(\"Yellow\", [Scope Health]@row:[Schedule Health]@row), \"Yellow\", IF(CONTAINS(\"green\", [Scope Health]@row:[Schedule Health]@row), \"Green\", \"//www.santa-greenland.com/community/discussion/111658/\")))<\/p>

Hope this helps!<\/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":112097,"type":"question","name":"If(contains) Function Not Giving True Value","excerpt":"I am having a problem with my If(Contains) function. I cannot get it to give me the True value no matter what I try. My if true value I want it to pull is the value that is listed in the \"Task\" cell. Here is a screenshot of the formula and data: =IF(CONTAINS(2, Level@row), Task@row, \"fail\")","snippet":"I am having a problem with my If(Contains) function. I cannot get it to give me the True value no matter what I try. My if true value I want it to pull is the value that is listed…","categoryID":322,"dateInserted":"2023-10-24T19:13:54+00:00","dateUpdated":null,"dateLastComment":"2023-10-24T19:58:05+00:00","insertUserID":168092,"insertUser":{"userID":168092,"name":"Kurt T.","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20T.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T20:46:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168092,"lastUser":{"userID":168092,"name":"Kurt T.","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20T.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T20:46:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3396353519,"url":"https:\/\/community.smartsheet.com\/discussion\/112097\/if-contains-function-not-giving-true-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112097\/if-contains-function-not-giving-true-value","format":"Rich","lastPost":{"discussionID":112097,"commentID":401485,"name":"Re: If(contains) Function Not Giving True Value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401485#Comment_401485","dateInserted":"2023-10-24T19:58:05+00:00","insertUserID":168092,"insertUser":{"userID":168092,"name":"Kurt T.","url":"https:\/\/community.smartsheet.com\/profile\/Kurt%20T.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-24T20:46:17+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ATGC4I4EG9FQ\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/ATGC4I4EG9FQ\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/ATGC4I4EG9FQ\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/ATGC4I4EG9FQ\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/ATGC4I4EG9FQ\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/ATGC4I4EG9FQ\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-24T19:57:48+00:00","dateAnswered":"2023-10-24T19:31:45+00:00","acceptedAnswers":[{"commentID":401482,"body":"

@Kurt T.<\/a> <\/p>

The contains function seems to be having an issue using a number as the reference data. I tried it with text instead of a number and the contains works. However, since you are referencing just a number, you do not need the contain function. You can write the logical expression directly. Try the formula below.<\/p>

=IF(Level@row=2,Task@row,\"fail\")<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions