duplicate detection assitance

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

Find more posts tagged with

Accepted answers

Paul Newcome

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

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

100% thank you!😁

All comments

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")

Frank Hammond

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

Happy to help. 👍️

Frank Hammond

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

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

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

Frank Hammond

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

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

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

100% thank you!😁

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(

Frank Hammond

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.