Trigger automation based on a count or multiples of a count
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?
Best Answers
-
Hi @TimMelton
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.
=SUMIF([Row ID]:[Row ID], <=[Row ID]@row, Comments:Comments)
Then, the "INT( /5)" helper column with the following formula returns results like 0, 0, 1,1, 2, and so on.
=INT([Cumulative Comments]@row / 5)
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
-
Hello.. that makes sense.. thanks much, I'll give that a try
Answers
-
Hi @TimMelton
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.
=SUMIF([Row ID]:[Row ID], <=[Row ID]@row, Comments:Comments)
Then, the "INT( /5)" helper column with the following formula returns results like 0, 0, 1,1, 2, and so on.
=INT([Cumulative Comments]@row / 5)
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
-
Hello.. that makes sense.. thanks much, I'll give that a try
-
Happy to help!😁
