Formula to find total amount assigned to someone?

kioshi43

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?

Genevieve P.

Hi @kioshi43

Based on your sheet set-up and multi-select 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 multi-select column per-building.

=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