% Allocation Formula for Assigned To column

The first bit would look something like this (put it in a separate column):

=IF(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], @cell = [Assigned To]@row) > 1, 1)

Then you can use conditional formatting based on this new column to highlight the Assigned To column where any row contains "1" in the new column.

As for the second bit, we would only need to make some minor tweaks to the above, but those tweaks are going to depend on...

Exactly what type of column is Assigned To? Contact w/multiple people allowed? Multi-select Dropdown? Text/number?

Hi @Paul Newcome ,

The first formula worked, thank you!

Exactly what type of column is Assigned To? Contact w/multiple people allowed? Multi-select Dropdown? Text/number?

The 'Assigned To' column is a 'Contact List' column type and the Allow multiple contacts per cell box is checked under properties.

Curious what this formula will look like if there are two people assigned in a cell and one person is over allocated, while the other is not..would the Assigned To column turn red either way per the conditional format?

It would turn red if either one, the other, or both are over 100%, but this is what the formula would look like for that:

=IF(OR(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, [Assigned To]@row) > 1, SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS([Assigned To]@row, @cell) > 1), 1)

Thank you for your reply! I inserted the formula you just gave me and I am receiving a column error: #Incorrect Argument Set.

The 'Assigned To' column is a 'Contact List' column type and the Allow multiple contacts per cell box is checked under properties.

The '% Allocation' column is a 'Text/Number' column type.

Can you please help me with this?

Also, if 5 or more people are assigned in a single cell and 2 or 3 out of the five are over 100% allocation, is there a formula or way to easily identify which person is over allocated? So if person X has 100 tasks (5%, 10%, 6%...etc) and the other 4 people have the same amount (but different) of tasks and percentages, other than filtering by five peoples name and seeing whose rows have turned red, is there a quicker way to identify?

Sorry about that. I missed a closing parenthesis.

I will have to get back to you on the second bit though. It may involve a separate sheet.

For some reason I am still getting the #INCORRECT ARGUMENT SET error. The % Allocation and Assigned To columns do highlight when I enter in the formula, but still receive the same error in my Formula column.

Thank you for your help on this.

Sorry again. I am really struggling with the parenthesis on this one for some reason:

=IF(OR(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, [Assigned To]@row)) > 1, SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS([Assigned To]@row, @cell)) > 1), 1)

Thank you the error went away but now whenever I assign any two stakeholders in the 'Assigned To' cell, the entire row turns red and this is without me adjusting % Allocation. I checked to see if somehow the % Allocation was being summed up together between the two people but even when combined it was less than 100%.

Following on my note from earlier. Please let me know if you can help.

Thank you!

Zain

Hi @Genevieve P. or @Paul Newcome

Any help would be appreciated.

Thank you!

Zain

Genevieve P.

Hi @Zain

There currently isn't a way to parse out a multi-select cell and search for each individual value separately within a column. The second SUMIFS statement is looking to see if that specific combination of names appears more than once (ex. both "Bob" and "Riley" in a cell together).

I would suggest creating two tasks with a single person assigned to each instead of having one tasks with two people.

Another option would be to search the cell for each of your possible selections and then check the % Allocation for each.

If the row both has "Bob" selected and if the SUMIFS of Bob being selected is greater than 100%, return 1.

Ex:

=IF(AND(HAS([Assigned To]@row, "Bob", SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, "Bob")) > 1), 1)

Then you can add many multiple IF statements together, one for each of your contacts:

=IF(AND(HAS([Assigned To]@row, "Bob", SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, "Bob")) > 1), 1) + IF(AND(HAS([Assigned To]@row, "Riley", SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, "Riley")) > 1), 1)

Once you've added together all possible people to search for, you can set up the Conditional Formatting rule to see if that formula column is greater than 1.

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Leibel S

@Zain

You can technically hard code for each possible added assigned to. Example below will check up to 2 contacts per cell.