Using COUNTIFS to automatically assign new rows to a person based off criteria

Justin Ramos

Hey all, I am working on a way to automatically assign newly added rows to a sheet based off a certain criteria. We have two managers. I'd like new rows to automatically assign to one manager then the other based off different scopes of work from a multi-select column. For example, I can only input 8 types of jobs and 8 types of another. I want to evenly divide the workload amongst the two managers so one would get 4 jobs with one type of scope and 4 for the other and same for the other manager.

To do this, I was thinking about using a COUNTIFS in a helper column to count the number of jobs on that day that are associated with a specific scope of work in order (1,2,3,4). Then, I was going to set-up a checkbox column to check if odd or even and assign the manager with an automation based off that check box. so far, I've only been able to come up with this formula: =COUNTIFS(Auto:Auto, @cell <= Auto@row, [Install Date]:[Install Date], @cell = [Install Date]@row) with another column with: =COUNT(DISTINCT(COLLECT([Job Scope]@row, [Job Scope]@row, OR(CONTAINS("CONV", @cell), CONTAINS("Flat Stock", @cell), CONTAINS("Shower", @cell)))))

These formulas aren't complete and I'm sure there is a better way to write it or combine it so I was hoping someone could help me with this or think of a better way to do it? The COUNTIFS above produces the 1,2,3,4 I'm looking for and the COUNT formula produces the scope of work I'm looking for but I can't seem to put it all together.

Genevieve P.

Hi @Justin Ramos

I'm having difficulty understanding how you are identifying the "Scope" or how you know when one row should be assigned to one manager and another row to the second manager.

If you're using a multi-select column for the Job Scope, you could use COUNTM to see how many Scopes are selected in that cell.

=COUNTM([Job Scope]@row)

Then you could add together the number of multi-select values that the current manager is assigned to for that specific day:

=SUMIFS([CountM Formula]:[CountM Formula], [Assigned To]:[Assigned To], [Assigned To]@row, [Install Date]:[Install Date], [Install Date]@row)

You could potentially use this SUMIFS structure to find out how many values in the multi-select column each manager is assigned to, and have an IF statement return which one should be assigned to the next row (which one has the least assigned for that day), like so:

=IF(SUMIFS(manager 1) > SUMIFS(manager 2), "Manager 2", "Manager 1")

=IF(SUMIFS([CountM Formula]:[CountM Formula], [Install Date]:[Install Date], [Install Date]@row, [Assigned To]:[Assigned To], "Manager 1") > SUMIFS([CountM Formula]:[CountM Formula], [Install Date]:[Install Date], [Install Date]@row, [Assigned To]:[Assigned To], "Manager 2"), "Manager 2", "Manager 1")

Then your workflow can use this column as a Condition Block, so if the cell says "Manager 2" it will input Manager 2 into the Assigned To column.

If this isn't what you're looking to do, it would be helpful to see a screen capture of the sheet, but please block out sensitive data.

Cheers,

Genevieve

Justin Ramos

I found an ok workaround as of now. Below is the formula for

Scope 1 column:

=IFERROR(MATCH(Auto@row, COLLECT(Auto:Auto, [Install Date]:[Install Date], [Install Date]@row, [Job Scope]:[Job Scope], OR(CONTAINS("CONV", @cell), CONTAINS("Flat Stock", @cell), CONTAINS("Shower", @cell))), 0), "")

Scope 2 column:

=IFERROR(MATCH(Auto@row, COLLECT(Auto:Auto, [Install Date]:[Install Date], [Install Date]@row, [Job Scope]:[Job Scope], AND(NOT(CONTAINS("CONV", @cell)), NOT(CONTAINS("Flat Stock", @cell)), NOT(CONTAINS("Shower", @cell)))), 0), "")

Then 2 other columns with:

=IFERROR(IF(ISODD([Scope 1]@row), 1, 2), "")

=IFERROR(IF(ISODD([Scope 2]@row), 1, 2), "")

Finally, I added an automation if checked one, assign manager to row and if checked 2, assign other manager. Not sure if this will work just yet but waiting to see how it unfolds on our calendar to see if it is a good solution