# 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

• ✭✭✭✭✭✭

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.

• ✭✭✭

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?

Janice

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

Happy to help!

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.

• ✭✭✭

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

• ✭✭✭✭✭✭

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!