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?

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • kioshi43
    kioshi43 ✭✭✭

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!