COUNTIFS+CONTAINS+AND

I am trying to write a sheet summary formula to count the number of rows where: Sprint Name contains "Maintenance", Date Resolved is not blank, and Bug or Request equals "Bug"

[Sprint Name]:[Sprint Name], CONTAINS("Maintenance")

and

[Date Resolved]:[Date Resolved], <>""

and

[Bug or Request]:[Bug or Request], "Bug"

I tried the following: =COUNTIFS([Sprint Name]:[Sprint Name], CONTAINS("Maintenance"), [Date Resolved]:[Date Resolved], <>"", [Bug or Request]:[Bug or Request], "Request") but I receive an #INCORRECT ARGUMENT SET error.

Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    CONTAINS() must include the range to compare. You can use @cell in this instance.

    =COUNTIFS([Sprint Name]:[Sprint Name], CONTAINS("Maintenance", @cell), [Date Resolved]:[Date Resolved], <>"", [Bug or Request]:[Bug or Request], "Request")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Is the column you are inserting the formula into formatted as a date column?<\/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":108786,"type":"question","name":"Can't Save Field Logic Updates in Dynamic View","excerpt":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues\/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…","snippet":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It…","categoryID":322,"dateInserted":"2023-08-10T14:14:23+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:42:12+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T15:49:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-10T17:56:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":19,"score":null,"hot":3383364395,"url":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":108786,"commentID":390063,"name":"Re: Can't Save Field Logic Updates in Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390063#Comment_390063","dateInserted":"2023-08-10T16:42:12+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-10T17:56:43+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\/8C0H47JEM9MT\/fieldlogic.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"fieldlogic.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T15:49:43+00:00","dateAnswered":"2023-08-10T14:51:23+00:00","acceptedAnswers":[{"commentID":390035,"body":"

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

I hope you're well and safe!<\/p>

You must add\/change the Rule #1 name.<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":443,"urlcode":"dynamic-view","name":"Dynamic View"}]},{"discussionID":108759,"type":"question","name":"Help with formula","excerpt":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the number of dropdown items from. This is the sheet that i want the formula written in to return an output. Currently I have =IF(HAS({centers},…","snippet":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the…","categoryID":322,"dateInserted":"2023-08-09T20:13:45+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T17:17:45+00:00","insertUserID":161673,"insertUser":{"userID":161673,"name":"Lauren Hughes","url":"https:\/\/community.smartsheet.com\/profile\/Lauren%20Hughes","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WSHqXkuSMHM!nddov_PnFbs!Fw3jLOwOmG0","dateLastActive":"2023-08-10T17:37:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3383301690,"url":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108759,"commentID":390080,"name":"Re: Help with formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390080#Comment_390080","dateInserted":"2023-08-10T17:17:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+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\/CM7LO30OUR2Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:16:38+00:00","dateAnswered":"2023-08-09T21:23:23+00:00","acceptedAnswers":[{"commentID":389908,"body":"

Try this:<\/p>

=COUNTM(COLLECT({July}, {Centers}, HAS(@cell, Center@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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"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