Associated cost for each consultant
Hello, so I've created a sheet to record a project that has multiple consultants working on it. I would like to be able to choose one or multiple consultants for a task, their time spent and automatically their associated cost is calculated.
Any help would be greatly appreciated.
Best Answer

Hey @gregorc3
Intro
You would want to create columns for Task, Consultant, Time, Hourly Rate, and Total Cost and also a second sheet with Consultants and Hourly Rate. One of the simpler ways to deal with it is to add indented rows below each task and list all the possible consultant. This way each consultant can have a cell for hours and hourly rate.
Making The Sheet
Task
This will be manually entered for the parent rows and in the children rows you want to use the formula below.
=PARENT()
Consultant
You will need to enter in the list of consultants under each task. In the parent rows you can use this formula so it will only display the names of the people with hours entered
=JOIN(COLLECT(CHILDREN(), CHILDREN([Time (hours)]@row), >0), ", ")
Time (hours)
Make this column a dropdown to help with manually entering the information and in the parent row use the formula below
=SUM(CHILDREN())
Hourly Rate
For the hourly rate ;you will want to make a second sheet with all the Consultants and Hourly Rates so you can reference them and automatically pull in the rates. In the main sheet use the formula below and make sure it's converted into a column formula (rightclick on the cell after inputting it and you will see the option there)
=IF(COUNT(CHILDREN()) > 0, "", INDEX({Second Sheet Range 1}, MATCH(Consultant@row, {Second Sheet Range 2})))
Range 1 should be the Hourly Rate column
Range 2 should be the Consultant column
Total Cost
For this column you will want to use the formula below and convert that to a column formula as well.
=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), [Hourly Rate]@row * [Time (hours)]@row)
Final Product
The ending result should look something similar to below (top is expanded and bottom is collapsed). Depending on the number of consultants that you have you can either input all the consultants under each task or just the ones you know are involved. If you need to input any further information about each Consultant use the same method as you did for the Hourly Rate.
Answers

Hey @gregorc3
Intro
You would want to create columns for Task, Consultant, Time, Hourly Rate, and Total Cost and also a second sheet with Consultants and Hourly Rate. One of the simpler ways to deal with it is to add indented rows below each task and list all the possible consultant. This way each consultant can have a cell for hours and hourly rate.
Making The Sheet
Task
This will be manually entered for the parent rows and in the children rows you want to use the formula below.
=PARENT()
Consultant
You will need to enter in the list of consultants under each task. In the parent rows you can use this formula so it will only display the names of the people with hours entered
=JOIN(COLLECT(CHILDREN(), CHILDREN([Time (hours)]@row), >0), ", ")
Time (hours)
Make this column a dropdown to help with manually entering the information and in the parent row use the formula below
=SUM(CHILDREN())
Hourly Rate
For the hourly rate ;you will want to make a second sheet with all the Consultants and Hourly Rates so you can reference them and automatically pull in the rates. In the main sheet use the formula below and make sure it's converted into a column formula (rightclick on the cell after inputting it and you will see the option there)
=IF(COUNT(CHILDREN()) > 0, "", INDEX({Second Sheet Range 1}, MATCH(Consultant@row, {Second Sheet Range 2})))
Range 1 should be the Hourly Rate column
Range 2 should be the Consultant column
Total Cost
For this column you will want to use the formula below and convert that to a column formula as well.
=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), [Hourly Rate]@row * [Time (hours)]@row)
Final Product
The ending result should look something similar to below (top is expanded and bottom is collapsed). Depending on the number of consultants that you have you can either input all the consultants under each task or just the ones you know are involved. If you need to input any further information about each Consultant use the same method as you did for the Hourly Rate.
Help Article Resources
Categories
Check out the Formula Handbook template!