duplicate detection assitance

Frank Hammond
Frank Hammond ✭✭
edited 07/14/23 inFormulas and Functions

If anyone is looking for something similar here was my answer and worked 100%


=IF(AND([email protected]<> "", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell =[email protected]日期:日期、@cell =[email protected])) > 3), "Red", "")


Once the cell worded red, conditional formatting turned the offending cells red with white text for me on the 4th instance.


Good day all,

I am working on a project that requires data to not exceed 3 entries per day. The reason for this is that the list of entries can grow long at times and missed.

I have started working on a formula for it. However I am met with the lovely Unparseable error.

I am sure I goofed this up 100% I am still learning.

What I need it to do is if there are three of the same names in the Agent column to condition it to highlight anything beyond 3 in red to make the user stop and review it first before proceeding. I hope that makes sense. Any help on this would be amazing!


In the sample provided, Test 1 has made it 3 times, so in essence the 4th entry I would like it to highlight to red.

=COUNTIFS([Agent Column]:[Agent Column], [Agent Column]@row, [Date Column]:[Date Column], [Date Column]@row) > 3


image.png


Best Answers

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You need to make sure you are using the same column names in the formula that you have in your sheet. You also need to use an IF statement to say that IF the COUNTIFS is greater than three, output "desired indicator".

    =IF(COUNTIFS(..........)> 3, "desired indicator")

  • Thank you, Paul. That made a lot of sense.

    This function is solid.

    =IF(COUNTIFS(Agent:Agent,[email protected], Date:Date,[email protected]) > 3, "Red")

    Conditional formatting took care of the rest!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • Frank Hammond
    Frank Hammond ✭✭
    edited 07/14/23

    Paul,


    Since I have you here, one last little itty bitty thing I overlooked. It really isn't about the names that need to be monitored, but the total number of sales.


    How would it be best to craft a formula to monitor either or?

    So say Test 1 sold three items and that is the max they get credit for in any given date. I would want to flag it for that before the 3 instance limit.


    Here is the formula now. I moved it to stay blank if there is no data in the agent field.

    =IF(AND([email protected]<> "", COUNTIFS(Agent:Agent,[email protected], Date:Date,[email protected]) > 3), "Red", "")

    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need a COUNTM/COLLECT combo instead of the COUNTIFS.

    COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell =[email protected]))

  • with this, it brought back a numeric count, but I still need to ensure the date is monitored as well; since this formula is only looking at the agent and sold columns.

    The main trigger here for this would be as follows:

    Date / Product Sold / Agent

    If date is the same where there have been either 3 distinct products sold by 1 agent or the agent name appearing three times in any given date.

    I hope that makes sense.

    Here is a SS of what I am trying to get too.

    Test 1 sells three in one shot, that meets the conditions if a 4th enters it gets highlighted

    Test 2 sells three, but is over two separate instances on the same date it gets highlighted.

    Test 3 sells three, but is on a different date, so it is ignored until there are 2+ more entries for 7/14

    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Right. That was to be inserted in place of the COUNTIFS in your existing formula, but I did forget to add the date piece in.

    COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell =[email protected]日期:日期、@cell =[email protected]))

  • Frank Hammond
    Frank Hammond ✭✭
    Answer ✓

    Bro! that was it! Thank you, I would not thought to use CountM(Collect for this at all.


    100% thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.


    The COUNTM function allows us to count the number of entries in a multi-select dropdown (or contact) field, and the COLLECT function basically turns an other function into an IFS.


    COUNTM(COLLECT(

    is equivalent to the non-existent

    COUNTMIFS(


    Kind of like another popular one

    JOIN(COLLECT(

    is essentially

    JOINIFS(


    And for a clearer point of reference

    COUNTIFS(

    is the same as

    COUNT(COLLECT(

  • amazing, I have that saved on my one notes for future reference!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册template!
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/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":108438,"type":"question","name":"NETWORKDAYS WITH AN IF??","excerpt":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","snippet":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","categoryID":322,"dateInserted":"2023-08-02T19:37:00+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T20:11:28+00:00","insertUserID":164426,"insertUser":{"userID":164426,"name":"JT_22002","url":"https:\/\/community.smartsheet.com\/profile\/JT_22002","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-03T11:32:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-03T12:00:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":17,"score":null,"hot":3382012708,"url":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108438,"commentID":388600,"name":"Re: NETWORKDAYS WITH AN IF??","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388600#Comment_388600","dateInserted":"2023-08-02T20:11:28+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-03T12:00:13+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T11:34:23+00:00","dateAnswered":"2023-08-02T20:11:28+00:00","acceptedAnswers":[{"commentID":388600,"body":"

I'm not sure I understand the goal. If you want to see the days between two column, you will need to already have a date in each of those columns. This seems to contradict stopping the count once one of the columns is populated. Can you provide more detail, or maybe a screenshot\/mockup of what you are looking for?<\/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"}]},{"discussionID":108410,"type":"question","name":"Using the \"Created Date\" field in formulas","excerpt":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…","snippet":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field…","categoryID":322,"dateInserted":"2023-08-02T13:45:01+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T17:51:15+00:00","insertUserID":141394,"insertUser":{"userID":141394,"name":"KGetchell","url":"https:\/\/community.smartsheet.com\/profile\/KGetchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T18:07:47+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-02T18:59:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":30,"score":null,"hot":3381985576,"url":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","format":"Rich","lastPost":{"discussionID":108410,"commentID":388557,"name":"Re: Using the \"Created Date\" field in formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388557#Comment_388557","dateInserted":"2023-08-02T17:51:15+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-02T18:59:20+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\/C0IGNY6CEA25\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:13:22+00:00","dateAnswered":"2023-08-02T15:09:48+00:00","acceptedAnswers":[{"commentID":388501,"body":"

The Created (date) field is not the issue in this instance. The problem is the data in your Invoiced Date column is actually text strings instead of date values.<\/p>


<\/p>

Try putting this into a date type column and using this new column in place of the Invoiced Date column in your original formula.<\/p>

=DATE(VALUE(MID([Invoiced Date (EP)]@row, FIND(\" \", [Invoiced Date (EP]@row) - 4, 4)), VALUE(LEFT([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) - 1)), VALUE(MID([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1, FIND(\"\/\", [Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1) - (FIND(\"\/\", [Invoiced Date (EP)]@row) + 1))))<\/p>"},{"commentID":388539,"body":"

Double check that you are putting it in a date type column. Can you also provide a screenshot of those same rows with the errors but also showing the Invoiced Date? It is working for me.<\/p>


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