Formula to find total amount assigned to someone?
Hi all,
I hit a bit of a stumper and I was hoping to see if someone might be able to help.
I need to find out how much square footage each person covers within a table, something similar to this:
Since multiple people can be assigned to a specific building, I would want to divide the square footage by the amount of people assigned to each individual building and then add the separate values. So in the example above, Debby, Jessie, and Ryan are all covering the south building (90,000 / 3)  they would start off with 30,000 each. But since Rachel and Ryan are also managing the East building, they would need to add their numbers as well (100,000 / 2). So Ryan would be assigned 80,000 (30,000 for South + 50,000 for East) square footage covered.
I could do this all manually but since there are multiple buildings (in a multiselect column) and people, I was hoping to find a self sustaining formula that could change if building assignments change. Is this possible?
Best Answer

Hi @kioshi43
Based on your sheet setup and multiselect columns, the way I would do this is to insert two helper columns in your orange sheet with the Buildings listed and Square footage.
1  Division
One helper column is to divide the Square Footage based on how many times it is listed in your blue sheet.
=[Square Footage]@row / COUNTIF({Buildings}, HAS(@cell, Buildings@row))
2  People Assigned
The second helper column is to list each person assigned in a multiselect column perbuilding.
=JOIN(COLLECT({People}, {Buildings}, HAS(@cell, Buildings@row)), CHAR(10))
Then when you have this detail in your Building sheet, you can use a SUMIF formula to SUM together the Division column IF the People Assigned for that row has the person's name in your blue sheet:
=SUMIF({People Assigned}, HAS(@cell, People@row), {Division})
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Answers

Hi @kioshi43
Based on your sheet setup and multiselect columns, the way I would do this is to insert two helper columns in your orange sheet with the Buildings listed and Square footage.
1  Division
One helper column is to divide the Square Footage based on how many times it is listed in your blue sheet.
=[Square Footage]@row / COUNTIF({Buildings}, HAS(@cell, Buildings@row))
2  People Assigned
The second helper column is to list each person assigned in a multiselect column perbuilding.
=JOIN(COLLECT({People}, {Buildings}, HAS(@cell, Buildings@row)), CHAR(10))
Then when you have this detail in your Building sheet, you can use a SUMIF formula to SUM together the Division column IF the People Assigned for that row has the person's name in your blue sheet:
=SUMIF({People Assigned}, HAS(@cell, People@row), {Division})
Let me know if this makes sense and works for you!
Cheers,
Genevieve

This works out great, thank you so much! I got so wrapped up in trying to work in the assignment sheet I blanked on utilizing the other sheet to help. Thank you!

No problem! I'm glad I could help. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!