# Trigger automation based on a count or multiples of a count

Options
✭✭✭✭

Hello. Im testing out triggering an automation based on a countif, but I cant figure out the logic to apply the automation as the countif increases. For instance. I want the automation to run each time we get 5 submittals via a form for the same category. Right now I'm using a metric sheet and a countif with a mod formula.. but that only works if we get exactly 5 submitted before the automation runs.. if we get 6, it fails. Any ideas on a formula that would account for that?

Tags:

• ✭✭✭✭✭✭
Options

Different numbers of comments come each time in the Comments columns, like 1,3,2,3,4,1, and so on.

First, I added a helper column, "Cumulative Comments," with the following formula, assuming we have a Row ID or Row No columns.

Then, the "INT( /5)" helper column with the following formula returns results like 0, 0, 1,1, 2, and so on.

To determine if you need to trigger the automation, you can check if the INT number changed from the one row before.

The formula to determine this is the following;

=IF([Row ID]@row > 1, IF(INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) < [INT( /5)]@row, 1, 0), 0)

The first IF prevents an error at the first row when we convert the formula to a column formula. Then, the INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) returns the INT column value one row before. So, the formula checked if the value is less than the current row's value. If we get more than five comments, we trigger the automation.

You can get the value in the previous row by combining Row ID or Row Number and the INDEX.

https://app.smartsheet.com/b/publish?EQBCT=f2fca89d42d94c1ba9df16302eac61b5

• ✭✭✭✭
Options

Hello.. that makes sense.. thanks much, I'll give that a try

• ✭✭✭✭✭✭
Options

Different numbers of comments come each time in the Comments columns, like 1,3,2,3,4,1, and so on.

First, I added a helper column, "Cumulative Comments," with the following formula, assuming we have a Row ID or Row No columns.

Then, the "INT( /5)" helper column with the following formula returns results like 0, 0, 1,1, 2, and so on.

To determine if you need to trigger the automation, you can check if the INT number changed from the one row before.

The formula to determine this is the following;

=IF([Row ID]@row > 1, IF(INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) < [INT( /5)]@row, 1, 0), 0)

The first IF prevents an error at the first row when we convert the formula to a column formula. Then, the INDEX([INT( /5)]:[INT( /5)], [Row ID]@row - 1) returns the INT column value one row before. So, the formula checked if the value is less than the current row's value. If we get more than five comments, we trigger the automation.

You can get the value in the previous row by combining Row ID or Row Number and the INDEX.

https://app.smartsheet.com/b/publish?EQBCT=f2fca89d42d94c1ba9df16302eac61b5

• ✭✭✭✭