% Allocation Formula for Assigned To column

Zain

Hi @Genevieve P.

Can you please help me come up with an IF formula to use in the % Allocation column for my sheet?

This is what I am trying to formulate:

If Bob (from the ‘Assigned To’ column) reaches greater than 100% allocation (from the sum of multiple rows), make the ‘Assigned To’ cell Fill in Red for wherever Bob’s name is. Example below shows over allocation at 101%. If you would recommend a different flag other then highlighting All of bobs name in red, please let me know. No budget for 10000 ft/resource management yet.

Also, is there another formula you can propose if there are multiple people in a single cell? How would % allocation work then?

Thank you for your help!

Zain

Leibel S

@Zain

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

=IF(COUNTM([Assigned To]@row) = 1, IF(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, [Assigned To]@row)) > 1, 1, ""), IF(COUNTM([Assigned To]@row) = 2, IF(OR(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, LEFT([Assigned To]@row, FIND(", ", [Assigned To]@row) - 1))) > 1, SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, MID([Assigned To]@row, FIND(", ", [Assigned To]@row) + 2, LEN([Assigned To]@row) - FIND(", ", [Assigned To]@row)))) > 1), 1, ""), ""))

However it makes a lot more sense to have another sheet with a separated list of each possible assigned to (single person per row) with a column that looks up the % Allocation for this specific employee, then in this sheet you can look up this person to see if they are marked as over allocated and change accordingly.

Paul Newcome

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?

Zain

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?

Paul Newcome

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)

Zain

Hi @Paul Newcome

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?

Paul Newcome

Sorry about that. I missed a closing parenthesis.

=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)

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

Zain

Hi @Paul Newcome

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.

Paul Newcome

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)

Zain

Hi @Paul Newcome

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%.

Zain

Hi @Paul Newcome

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

Thank you!

Zain

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.

=IF(COUNTM([Assigned To]@row) = 1, IF(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, [Assigned To]@row)) > 1, 1, ""), IF(COUNTM([Assigned To]@row) = 2, IF(OR(SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, LEFT([Assigned To]@row, FIND(", ", [Assigned To]@row) - 1))) > 1, SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], HAS(@cell, MID([Assigned To]@row, FIND(", ", [Assigned To]@row) + 2, LEN([Assigned To]@row) - FIND(", ", [Assigned To]@row)))) > 1), 1, ""), ""))

However it makes a lot more sense to have another sheet with a separated list of each possible assigned to (single person per row) with a column that looks up the % Allocation for this specific employee, then in this sheet you can look up this person to see if they are marked as over allocated and change accordingly.

Zain

@Genevieve P. and @Leibel Shuchat Thank you for your help on this! Agreed makes sense to have another sheet with the same task or activity so I can properly allocate per person.