What is wrong with my formula?

Probably something obvious...

=COUNTIF((Position2:Position136, (CONTAINS("Medical Assistant", @cell))), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell))))

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/13/21 Answer ✓

    Original Formula:


    =COUNTIF((Position2:Position136, (CONTAINS("Medical Assistant", @cell))), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell))))

    You went a little crazy on the parenthesis is the first thing I spot

    =COUNTIF(Position2:Position136, CONTAINS("Medical Assistant", @cell), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell

    You don't need the and statement. You need a countifs to put multiple criteria into this.

    =countifs(position2:position136,contains("Medical Assistant",@cell),[cleared to return?]2:[cleared to return?]136,or(contains("No",@cell),contains("Contingent",@cell

    and that should work I think

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/13/21 Answer ✓

    Original Formula:


    =COUNTIF((Position2:Position136, (CONTAINS("Medical Assistant", @cell))), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell))))

    You went a little crazy on the parenthesis is the first thing I spot

    =COUNTIF(Position2:Position136, CONTAINS("Medical Assistant", @cell), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell

    You don't need the and statement. You need a countifs to put multiple criteria into this.

    =countifs(position2:position136,contains("Medical Assistant",@cell),[cleared to return?]2:[cleared to return?]136,or(contains("No",@cell),contains("Contingent",@cell

    and that should work I think

  • Awesome that worked, thank you!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    It looks like you might be trying to do more than one criteria in COUNTIF. Have you tried constructing this as a COUNTIFS?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
I flipped the red and yellow<\/p>

=if(countif([due date]:[due date],<today()) = 0,\"Green\",if(count([due date]:[due date]) * .1 >countif([due date]:[due date],<today()),\"Yellow\",\"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":[]},{"discussionID":111265,"type":"question","name":"Calculating fines due based on calendar date","excerpt":"Hi, I am looking for help with calculating fines based on a missed due date, and the subsequent number of calendar days past the due date. Ideally, I would like the fine to calculate in two instances. 1) if the date in \"Y1Q1 Date Payment RCVD\" is greater than the due date, OR 2). the date is blank and today's date is…","snippet":"Hi, I am looking for help with calculating fines based on a missed due date, and the subsequent number of calendar days past the due date. Ideally, I would like the fine to…","categoryID":322,"dateInserted":"2023-10-05T17:03:50+00:00","dateUpdated":null,"dateLastComment":"2023-10-05T18:35:56+00:00","insertUserID":168047,"insertUser":{"userID":168047,"name":"Stitched","url":"https:\/\/community.smartsheet.com\/profile\/Stitched","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T18:27:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-05T20:38:02+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":17,"score":null,"hot":3393058186,"url":"https:\/\/community.smartsheet.com\/discussion\/111265\/calculating-fines-due-based-on-calendar-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111265\/calculating-fines-due-based-on-calendar-date","format":"Rich","lastPost":{"discussionID":111265,"commentID":398719,"name":"Re: Calculating fines due based on calendar date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398719#Comment_398719","dateInserted":"2023-10-05T18:35:56+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-05T20:38:02+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"}},"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\/M47QMCUFOQU2\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/M47QMCUFOQU2\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-05T20:33:37+00:00","dateAnswered":"2023-10-05T17:40:08+00:00","acceptedAnswers":[{"commentID":398693,"body":"

I think this will work. The first part of the formula handles if a payment was received but was past the due date, and the second part handles if TODAY() is past the due date but no payment shows as received. It should return blank if neither requirements are met, so it inherently solves the third example.<\/p>

Formula<\/strong><\/p>

=IF(NOT(ISBLANK([Y1Q1 Date Payment RCVD]@row)), IF([Y1Q1 Date Payment RCVD]@row > [Due Date]@row, [Y1Q1 Date Payment RCVD]@row - [Due Date]@row), IF(TODAY() > [Due Date]@row, TODAY() - [Due Date]@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":111252,"type":"question","name":"Convert Text to Date","excerpt":"I am looking for help to convert 20230818 ['Max Date' text formatted column] to read 08\/18\/2023 in the ['Last Activity Date' date formatted column]. However, the formula that I am using returns a value of #INCORRECT ARGUMENT SET. =DATE(VALUE(MID([Max Date]@row, 5, 2), (VALUE(RIGHT([Max Date]@row, 2), (VALUE(LEFT([Max…","snippet":"I am looking for help to convert 20230818 ['Max Date' text formatted column] to read 08\/18\/2023 in the ['Last Activity Date' date formatted column]. However, the formula that I am…","categoryID":322,"dateInserted":"2023-10-05T14:43:03+00:00","dateUpdated":null,"dateLastComment":"2023-10-05T16:30:55+00:00","insertUserID":165028,"insertUser":{"userID":165028,"name":"Heather P.","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Heather%20P.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jQ9z43MAzno!g06wZ9dLOEg!WMaAtvFiz-H","dateLastActive":"2023-10-05T20:23:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-10-05T20:45:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3393043438,"url":"https:\/\/community.smartsheet.com\/discussion\/111252\/convert-text-to-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111252\/convert-text-to-date","format":"Rich","lastPost":{"discussionID":111252,"commentID":398665,"name":"Re: Convert Text to Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398665#Comment_398665","dateInserted":"2023-10-05T16:30:55+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-10-05T20:45:58+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\/XEL3OVI7TIKD\/snag-853419.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XEL3OVI7TIKD\/snag-853419.png"},"alt":"Snag_853419.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-05T15:59:30+00:00","dateAnswered":"2023-10-05T15:14:46+00:00","acceptedAnswers":[{"commentID":398634,"body":"

Hi @Heather P.<\/a>,<\/p>

Try this:<\/p>

=RIGHT((LEFT([Max Date]@row, 6)), 2) + \"\/\" + RIGHT([Max Date]@row, 2) + \"\/\" + LEFT([Max Date]@row, 4)<\/p>

I tested and here are the results: <\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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