COUNTIFS+CONTAINS

I get the message UNPARASABLE, what am I doing wrong?

I want to count the non blank fields in rage 1, when in range 4 I have a certain value AND is the range 7 = OPEN value.

=COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1};<>"";[{Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}; CONTAINS("AG",@cell)];{Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7};="OPEN")

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@CaroCambre

    In your other formulas, do you use ; or , ?

    If you use ; then I think I can see the issue! My apologies, I left a comma in the HAS function. We'll want to swap that out for ; as well:

    =COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}; <>""; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}; "OPEN"; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}; HAS(@cell; "AG"))

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭

    Hello@CaroCambre

    You are using semicolons instead of commas to separate your conditions, the syntax is incorrect so the formula will not work. You also suddenly added a random CONTAINS() for some reason. You can keep going and specify which range and criteria you are looking for using the COUNTIFS() function without using CONTAINS().

    =COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}, <>"", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}, ="OPEN", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}, ="AG")

    Hope this Helps

  • CaroCambre
    edited 05/02/22
    2022-05-02 15_40_28-Window.png

    Is still not working, the range 4 is containing multiple values, is that the reason?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@CaroCambre

    If you're looking into a multi-select column, you can use theHAS Functionto search for one value:

    =COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}, <>"", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}, "OPEN", {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4},HAS(@cell, "AG"))

    If this hasn't worked, can you explain if you're getting an incorrect result or a formula error?

  • I get the message "Unparseable".

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@CaroCambre

    What are your language settings? Depending on your language, you'll want to use ; or ,

    In your original formula you had ;

    =COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}; <>""; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}; "OPEN"; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4};HAS(@cell, "AG"))

    If neither ; or , works, please post a screen capture of the formula open in your sheet, but block out sensitive data.

    Thanks!

    Genevieve

  • Both give same "unparseable" message.

    Hereby my screenshot:

    2022-05-02 15_40_28-Window.png


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@CaroCambre

    In your other formulas, do you use ; or , ?

    If you use ; then I think I can see the issue! My apologies, I left a comma in the HAS function. We'll want to swap that out for ; as well:

    =COUNTIFS({Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 1}; <>""; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 7}; "OPEN"; {Final RISK/ NON CONFORMITIES GROEP AERTSSE Range 4}; HAS(@cell; "AG"))

  • This is working! Thank you! :-)

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad we got there in the end.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
DATE(2020, 1, 1), [IRRF RECEIVED]@row = 0), [IMMUNISATION INCIDENT REPORT FORM SENT]@row + 10, IF(AND([IMMUNISATION INCIDENT REPORT FORM SENT]@row > DATE(2020, 1, 1), [IRRF…","categoryID":322,"dateInserted":"2022-12-05T10:10:21+00:00","dateUpdated":null,"dateLastComment":"2022-12-06T05:51:33+00:00","insertUserID":141269,"insertUser":{"userID":141269,"name":"Fialko66","url":"https:\/\/community.smartsheet.com\/profile\/Fialko66","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-06T05:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":141269,"lastUser":{"userID":141269,"name":"Fialko66","url":"https:\/\/community.smartsheet.com\/profile\/Fialko66","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-06T05:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":39,"score":null,"hot":3340543314,"url":"https:\/\/community.smartsheet.com\/discussion\/98394\/worday-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98394\/worday-formula","format":"Rich","lastPost":{"discussionID":98394,"commentID":353244,"name":"Re: WORDAY FORMULA","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353244#Comment_353244","dateInserted":"2022-12-06T05:51:33+00:00","insertUserID":141269,"insertUser":{"userID":141269,"name":"Fialko66","url":"https:\/\/community.smartsheet.com\/profile\/Fialko66","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-06T05:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-06T05:54:52+00:00","dateAnswered":"2022-12-05T19:01:39+00:00","acceptedAnswers":[{"commentID":353185,"body":"

You would drop it in in place of <\/p>

[Date Column]@row + 10<\/p>


<\/p>

Everything else should stay the same.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-06 05:54:52","updateUser":{"userID":141269,"name":"Fialko66","url":"https:\/\/community.smartsheet.com\/profile\/Fialko66","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-06T05:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":98409,"type":"question","name":"I have a formula that does not work now because of the new year.","excerpt":"This formula has been working just fine until the month of December and now returns #invalid Data Type\" I assume it is because it is looking for dates within this calendar year and not next calendar year? Does anyone have any suggestions on how I can fix this problem? The formula looks at the Determination Date and then…","categoryID":322,"dateInserted":"2022-12-05T16:45:56+00:00","dateUpdated":null,"dateLastComment":"2022-12-05T19:43:51+00:00","insertUserID":126337,"insertUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":126337,"lastUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":28,"score":null,"hot":3340531187,"url":"https:\/\/community.smartsheet.com\/discussion\/98409\/i-have-a-formula-that-does-not-work-now-because-of-the-new-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98409\/i-have-a-formula-that-does-not-work-now-because-of-the-new-year","format":"Rich","lastPost":{"discussionID":98409,"commentID":353207,"name":"Re: I have a formula that does not work now because of the new year.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353207#Comment_353207","dateInserted":"2022-12-05T19:43:51+00:00","insertUserID":126337,"insertUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-05T19:15:44+00:00","dateAnswered":"2022-12-05T19:11:41+00:00","acceptedAnswers":[{"commentID":353196,"body":"

@Christopher Flemings<\/a> <\/p>

=IF([CONDITIONAL LICENCE]@row, DATE(if(MONTH([Determination Date]@row)=12,YEAR([Determination Date]@row)+1,YEAR([Determination Date]@row)), MONTH([Determination Date]@row) + 1, DAY([Determination Date]@row)), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 19:15:44","updateUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":98391,"type":"question","name":"Help w\/multiple AND NOT ISBLANK date field formula","excerpt":"Hi team - I have 6 date column fields that are automatically formatted (record a date) when a Status field changes. Let's just call those columns: Date1 Date2 Date3 Date4 Date5 Date6 I created a check box \"helper column\" that I'd like to create a formula that looks across those fields to determine if ALL of them have a…","categoryID":322,"dateInserted":"2022-12-04T15:40:46+00:00","dateUpdated":null,"dateLastComment":"2022-12-05T14:52:40+00:00","insertUserID":118864,"insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":118864,"lastUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3340422806,"url":"https:\/\/community.smartsheet.com\/discussion\/98391\/help-w-multiple-and-not-isblank-date-field-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98391\/help-w-multiple-and-not-isblank-date-field-formula","format":"Rich","lastPost":{"discussionID":98391,"commentID":353142,"name":"Re: Help w\/multiple AND NOT ISBLANK date field formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353142#Comment_353142","dateInserted":"2022-12-05T14:52:40+00:00","insertUserID":118864,"insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-05T14:51:25+00:00","dateAnswered":"2022-12-04T15:45:20+00:00","acceptedAnswers":[{"commentID":353088,"body":"

Hey @jmo<\/a> <\/p>

Are your date fields contiguous (all side by side?). I'm assuming they are. If not, each date field will have to be added to the COUNTIFS individually<\/p>

=IF(COUNTIFS([Date1]@row:[Date6]@row, ISDATE(@cell))=6, 1)<\/p>

Will this work for you?<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 14:51:25","updateUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">