COUNTIFS HELP!

凯尔·贾勒特
凯尔·贾勒特 ✭✭✭
inFormulas and Functions

Hello,

I'm attempting to write a formula that says count if this branch is rendered but not if this column shows "PTB"... I got the first part to work and it counts if the branch is in there but cannot get it to not count those with PTB in the PTB? column. if you need any further explanation please let me know!

Logic:

Count if Branch is "Schererville Stracks 101" but not if PTB? column shows "PTB"


=COUNTIFS([Branch #]:[Branch #], "Schererville Stracks 101", [PTB?]:[PTB?], NOT(FIND("PTB", LOWER(@cell)) = "PTB"))

Tags:
· Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Part of the problem is that you are searching for capitalized text within lower case data. There is also an issue with that criteria set in that FIND generates a numeric value, but you are saying "equals text". Here is a different way to write that criteria that may be a little more simplified. Give it a shot and see how it works.


    =COUNTIFS([Branch #]:[Branch #], "Schererville Stracks 101", [PTB?]:[PTB?],< >“肺结核”)

    thinkspi.com

    · Share on Facebook Share on Twitter
  • 凯尔·贾勒特
    凯尔·贾勒特 ✭✭✭

    @Paul NewcomeThanks for the idea. I did actually try it that way and it wasn't working. For some reason this won't even count when the branch column is the Schererville Stracks 101 value. That's the only reason I went the more complex route. I wasn't even paying attention on the lower case values so thank you for that!

    Really dumb question because I know it looks for values but i'll ask anyway.. is it because I am pulling the information through a data mesh/this isn't a dropdown column?

    Any other ideas?

    · Share on Facebook Share on Twitter
  • 凯尔·贾勒特
    凯尔·贾勒特 ✭✭✭

    @Paul Newcomeit shouldn't matter but I figured I'd mention this is in the sheet summary where I am attempting to write this.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    写这一张总结领域不应该ake a difference.


    Double check that your spelling is correct. I notice there is a space after 101 in your formula. If there is no space in the cells afterwards, then it won't count. Try removing that and see if it works.

    thinkspi.com

    · Share on Facebook Share on Twitter
  • 凯尔·贾勒特
    凯尔·贾勒特 ✭✭✭

    @Paul Newcomestill no such luck unfortunately. Idk why that one is not working because in theory that is what we are saying. oddly confused why this doesn't work.

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@Kyle Jarrett

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • 凯尔·贾勒特
    凯尔·贾勒特 ✭✭✭

    Hi@Andrée Starå,

    I was getting some help from my smartsheet customer success rep. and when I went in to see what she had done this morning I changed a few things in the formula to what I thought it should be and it is working now. Idk why it decided to work now but I got it to work using the below formula with multiple successes.

    =COUNTIFS([Branch #]:[Branch #], "Southlake 036", [PTB?]:[PTB?], <>"PTB")

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Kyle JarrettOk. Glad you got it working!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册template!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Hi @steinkj<\/a>, change the \"HAS\" to an equal.<\/p>


<\/p>

=COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, \"Often\")<\/p>"},{"commentID":382723,"body":"

Thank you, as always I was trying to make it too hard!!!!<\/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":106939,"type":"question","name":"How to extract part of a cell and remove the rest","excerpt":"Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and I want to extract to another column only the text before the word 'request '. Could you please help me how to do this? Thank you so much!","snippet":"Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and…","categoryID":322,"dateInserted":"2023-06-27T09:11:14+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T09:51:15+00:00","insertUserID":143328,"insertUser":{"userID":143328,"name":"Christiana Gkini","url":"https:\/\/community.smartsheet.com\/profile\/Christiana%20Gkini","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf-hNm8WhOgG9NXB7Zou6oQaUyDHZZ2iwrHmrrz=s96-c","dateLastActive":"2023-06-28T10:00:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T10:04:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3375804749,"url":"https:\/\/community.smartsheet.com\/discussion\/106939\/how-to-extract-part-of-a-cell-and-remove-the-rest","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106939\/how-to-extract-part-of-a-cell-and-remove-the-rest","format":"Rich","lastPost":{"discussionID":106939,"commentID":382775,"name":"Re: How to extract part of a cell and remove the rest","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382775#Comment_382775","dateInserted":"2023-06-28T09:51:15+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T10:04:09+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":"2023-06-28T09:27:10+00:00","dateAnswered":"2023-06-27T11:27:01+00:00","acceptedAnswers":[{"commentID":382494,"body":"

Hi @Christiana Gkini<\/a>,<\/p>

If the format of the entries is consistent then you can use a formula similar to this:<\/p>

=LEFT([Column to check]@row, (FIND(\"request\", [Column to check]@row) - 2))<\/p>

Example:<\/p>

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

Obviously you will probably need to change the \"Column to check\" in the formula, but this should be straightforward enough.<\/p>

Hope this helps; any questions or issues then just post! 😊<\/span><\/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":106973,"type":"question","name":"SUMIFS with CONTAINS","excerpt":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column. I've tried various versions of this. =SUMIFS(CONTAINS({Activity Description}, \"CF\", {Ext Price}) =SUMIFS({Activity Description}, CONTAINS(CF), {Ext…","snippet":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column.…","categoryID":322,"dateInserted":"2023-06-27T19:19:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T21:26:52+00:00","insertUserID":162846,"insertUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162846,"lastUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3375795958,"url":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106973,"commentID":382705,"name":"Re: SUMIFS with CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382705#Comment_382705","dateInserted":"2023-06-27T21:26:52+00:00","insertUserID":162846,"insertUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+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":"2023-06-28T09:44:38+00:00","dateAnswered":"2023-06-27T19:47:23+00:00","acceptedAnswers":[{"commentID":382674,"body":"

Try this:<\/p>

=SUMIFS({Ext Price}, {Activity Description}, CONTAINS(\"CF\", @cell))<\/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

  • Using Countif to count Often and Very Often,

    however I can't get it to work the Often is also counting the Very Often: =COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, HAS("Often", @cell), NOT(HAS("Very", @cell)))

  • How to extract part of a cell and remove the rest

    Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and I want to extract to another column only the text before the word 'request '. Could you please help me how to do this? Thank you so much!

  • SUMIFS with CONTAINS

    Hello, I want to write a formula that SUMS charges ("Ext Price" column) from all item descriptions that contain a word ("CF") from an accounting "Activity Description" column. I've tried various versions of this. =SUMIFS(CONTAINS({Activity Description}, "CF", {Ext Price}) =SUMIFS({Activity Description}, CONTAINS(CF), {Ext…