Formula to find total amount assigned to someone?

Options

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?

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • kioshi43
    kioshi43 ✭✭✭
    Options

    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!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem! I'm glad I could help. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!