Using COUNTIFS to automatically assign new rows to a person based off criteria
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.