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!
Best Answers
-
gstotts Employee
Try using a CONTAINS() statement in your criteria for the COUNTIF:
=条件统计(Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))
-
Genevieve P. Employee Admin
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
-
gstotts Employee
Try using a CONTAINS() statement in your criteria for the COUNTIF:
=条件统计(Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))
-
Tony Fronza ✭✭
Thank you very much@gstotts!
-
Tony Fronza ✭✭
@gstotts对不起,最后一个问题,我可以添加一个或函数n to have the formula search for multiple criteria? I tried adding OR to the formula you shared but I'm receiving an error message.
-
gstotts Employee
I think this should work but haven't had a chance to test myself.
Example: to return a count if the Solution video or In Person Event is contained in the field:
=条件统计(Level III Request]:[Level III Request], OR(CONTAINS("Solution Video", @cell), CONTAINS("In Person Event", @cell))
OR Documentation:https://help.smartsheet.com/function/or
-
Tony Fronza ✭✭
OK, I tried that and it returns a count, but it's incorrect (it lists 1 when it should be 2 since both options are listed in the sheet).
-
gstotts Employee
Ok. Above would only work for counting it once if either of those are present -- not counting how many of them are present in each. I'd have to think about that one a bit.
-
gstotts Employee
You could just count them individually and add them together I suppose:
=条件统计(Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))+COUNTIF([Level III Request]:[Level III Request], CONTAINS("In Person Event", @cell))
or (written with sum instead of +):
=SUM(COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell)), COUNTIF([Level III Request]:[Level III Request], CONTAINS("In Person Event", @cell)))
-
Tony Fronza ✭✭
@gstottsthanks, but these aren't changing the totals for some reason (it still reflects 1 when both criteria exist).
-
Hello all, I am trying all of the formulas above (and others) but I am still struggling to get a final count of each Source category (covid, lessons learned, project team, etc.). As you can see on the left, the IF, SUMIF, COUNTIF, SUMIFS, and COUNTIFS formulas are not counting correctly (column 2). See Column 3 which the actual values. Thank you in advanced.
-
Genevieve P. Employee Admin
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
-
@Genevieve P.thank you so much! This work flawlessly.
-
Genevieve I have one more that is stumping me. I need to create a low (0-182); medium (183-365) and high (365+) Age formula and =IF(Age@row<182,"LOW"), IF(AND(Age@row>=MIN(183)),VALUE(<=MAX(365)), "MEDIUM", IF(Age@row>=MIN(366)"HIGH")))) is not working.
Help Article Resources
Categories
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":"