Workflow or function that alerts me when something repeats

Jamie Paraf

I would like to set up an automation or function that alerts me when something is repeated in a sheet 3 times in a 30-day period. The trigger could be taken from a drop down menu, i.e the location "ABC Lane" has been chosen in a row from the drop down on 06/01, 06/05, and 06/21" . I do not see something like this in the automation field and I am unsure if there is a formula/function that could accomplish this.

Paul Newcome

Do you record the date when the dropdown is selected?

Jamie Paraf

Yes. It is recorded in a column (separate from the drop down list) for every row.

Paul Newcome

Ok. In a flag or checkbox type column, you would enter something along the lines of this:

=IF(COUNTIFS(Location:Location, Location@row, [Date column]:[Date Column], @cell>= TODAY(-30)) >= 3, 1)

Basically we count the number of rows that have a matching location and a date within the past 30 days and say that if that count is 3 or more then check/flag.

Jamie Paraf

Thank you! And if I wanted to add a second criteria to the formula, can I use an AND function here? For example, if the location appears 3 times in a 30 day period AND the classification of the location is either "reportable" or "investigated", where would I pull this into the formula?

Paul Newcome

It depends on where it needs to be incorporated. Are you wanting it to be a criteria for the COUNTIFS, or are you meaning if the classification on the existing row is one of those two options?

Jamie Paraf

The latter. Basically I want something that does: If the "location" is repeated 3 times in a 30 day period AND this repeated location is also classified as "reportable" or "investigated", then the flag column will be flagged.

Paul Newcome

Ok. Try this...

=IF(OR(Classification@row = "Reportable", Classification@row = "Investigated"), IF(COUNTIFS(Location:Location, Location@row, [Date column]:[Date Column], @cell>= TODAY(-30)) >= 3, 1))

Jamie Paraf

Thanks!

Paul Newcome

Happy to help. 👍️