# Calculating costs for tasks with multiple assigned reources

✭✭✭✭✭

Hi,

I am using the following formula to calculate the effort costs for a task. It references a list of resources with allocated rates. For a task with one resource assigned, this work fine.

=[Effort (hrs)]@row * INDEX({Rates}, MATCH([Assigned To]@row, {Names}, 0), 6)

However its not good for tasks with multiple resources assigned. How can it split out each resource assigned to a task within the formula and then calculate the effort costs for each?. I know each resource name is separated by a comma.

Thanks

Simon

Tags:

• ✭✭✭✭✭✭

Hey @Simon Cowx,

Can you please add some screenshots? It will help me understand the issue better.

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

• ✭✭✭✭✭✭

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭✭✭✭

Sure, see below:

• ✭✭✭✭✭

Rate card is below:

• ✭✭✭✭✭✭

Hey @Simon Cowx,

The only way I know how to do that is to use helper columns and have 1 name in each column, then the Vlookup will be able to find them.

@Andrée Starå has a lot more experience than me, he might have a solution.

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

• ✭✭✭✭✭

Hi Itai, thanks for your reply, this has let me get to a solution, through also using a formula suggested in a previous thread. The formula below strips out the name from the resource list. In the case below, the second name in comma delimited list,

=IFERROR(MID([Assigned To]@row + ",", FIND("~", SUBSTITUTE([Assigned To]@row + ",", ",", "~", 1)) + 1, FIND("~", SUBSTITUTE([Assigned To]@row + ",", ",", "~", 2)) - FIND("~", SUBSTITUTE([Assigned To]@row + ",", ",", "~", 1)) - 1), "")

This works fine, but hopefully Smartsheet will provide a solution to pick out resource names a little more easily in the future!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!