Assign a reviewer to a task based on which reviewer has the fewest open tasks

William Tremblay

I am trying to assign a reviewer to a task based on which reviewer has the fewest open tasks.

I have the following formulas working, and trying to hopefully use them together for automating the auto assign function:

1. Checkbox indicating a task is complete (no longer open):
   1. =IF(ISDATE([Executive Review Complete]@row), 1, 0)
2. Counting the number of open tasks:
   1. =COUNTIFS([Executive Reviewer]:[Executive Reviewer], "=XYZ Person", [Complete Is a Date]:[Complete Is a Date], "=0")
3. Displaying the name of the Executive Reviewer if that has the lowest number of open cases out of three:
   1. =IF(AND([Open Reviews]@row < [Open Reviews]31, [Open Reviews]@row < [Open Reviews]32), [Exec Reviewer]@row, " ")

• Note: the above Exec Reviewer is a separate column from Executive Reviewer, and is a helper column for this purpose.

I am stuck here. My end goal is to assign the next submitted row (from an associated form) to the Exec Reviewer with the least open tasks.

Thank you in advance for your help.

Genevieve P.

Hi @William Tremblay

You're close! Based on your final formula, it looks like you're returning one name and blank cells at the top of this helper column. Instead, you'll want to use a column formula to return the name with the lowest tasks throughout the entire column. This way you can use the value as a Condition in an automated workflow.

I would adjust your final formula to be this, instead:

=INDEX([Exec Reviewer]:[Exec Reviewer], MATCH(MIN([Open Reviews]:[Open Reviews]), [Open Reviews]:[Open Reviews], 0))

This will find the Exec Reviewer that has the least number of Open Reviews and put their name in the whole column:

Then because this name will appear with any new row created, you can use it as a Condition within an Assign People workflow:

The Condition looks at the current, newly added row to see the value for that row. It won't be able to check one specific cell, which is why I adjusted your formula to be a column formula.

Let me know if this makes sense or if you need any further clarification!

Cheers,

Genevieve

Genevieve P.

Hi @William Tremblay

You're close! Based on your final formula, it looks like you're returning one name and blank cells at the top of this helper column. Instead, you'll want to use a column formula to return the name with the lowest tasks throughout the entire column. This way you can use the value as a Condition in an automated workflow.

I would adjust your final formula to be this, instead:

=INDEX([Exec Reviewer]:[Exec Reviewer], MATCH(MIN([Open Reviews]:[Open Reviews]), [Open Reviews]:[Open Reviews], 0))

This will find the Exec Reviewer that has the least number of Open Reviews and put their name in the whole column:

Then because this name will appear with any new row created, you can use it as a Condition within an Assign People workflow:

The Condition looks at the current, newly added row to see the value for that row. It won't be able to check one specific cell, which is why I adjusted your formula to be a column formula.

Let me know if this makes sense or if you need any further clarification!

Cheers,

Genevieve

William Tremblay

Thank you, Genevieve!

My frustration was trying to target the one cell containing the next reviewer, and this solution will solve that problem.

Genevieve P.

No problem at all! It took me a while to figure out as well. I'm glad this will work for you.