How to lookup in two columns and do a sum from another column?

Hi All,

Reference attached screenshot.

The purpose is to calculate the total hours (Col 3) taught by Instructor A/B/C, etc. in Col 1 and Col 2.

Is there a formula or function that can do that? Or is there a better way to do this?

Thanks in advance for any guidance on the above.

Janice


Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Janice Phua

    I hope you're well and safe!

    Try something like this.

    =SUMIFS([Course Hours]:[Course Hours], [Instructor Name / Vendor]:[Instructor Name / Vendor], "Andrée Starå", [Co-Instructor]:[Co-Instructor], "Andrée Starå")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Janice Phua
    Janice Phua ✭✭✭

    Hi Andrée,

    Thank you, the first part of the formula works.

    Think I need something like sum the course hours if "Andrée Starå" is found in either [Instructor Name / Vendor] or [Co-Instructor]:[Co-Instructor] columns.

    Could you help?

    Thanks in advance!

    Janice

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Janice Phua,

    This formula should work for you

    =SUMIFS([Course Hours]:[Course Hours], [Instructor Name / Vendor]:[Instructor Name / Vendor], "Andrée Starå") + SUMIFS([Course Hours]:[Course Hours], [Co-Instructor]:[Co-Instructor], "Andrée Starå")

    It totals the hours from instructor and co-instructor and then adds them together.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Janice Phua

    Happy to help!

    I saw that Nick had answered already!

    Let me know if I can help with anything else!

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Janice Phua
    Janice Phua ✭✭✭

    Thank you both and one more question:

    What happen if the names enter in [Instructor Name / Vendor] or [Co-Instructor]:[Co-Instructor] columns are inconsistent?

    Using my name as example: some might enter Phua Janice / Phua, Janice / Janice Phua, etc.

    I did a test and noticed once the name is different in the formula, it would work.

    Any idea how I can fix that?

    Thanks lots!

    Janice

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Janice Phua

    You're more than welcome!

    Yes, it won't work if it doesn't match fully. I'd recommend adding the options in a Dropdown column if possible.

    Would that work?

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!