duplicate detection assitance

Frank Hammond
Frank Hammond ✭✭✭
edited 07/14/23 in Formulas and Functions

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


=IF(AND(Agent@row <> "", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row)) > 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



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, Agent@row, Date:Date, Date@row) > 3, "Red")

    Conditional formatting took care of the rest!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • 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(Agent@row <> "", COUNTIFS(Agent:Agent, Agent@row, Date:Date, Date@row) > 3), "Red", "")


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

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

  • 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


  • 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 = Agent@row, Date:Date, @cell = Date@row))

  • 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 Formula Handbook template!