Associated cost for each consultant

gregorc3

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.

Devin Lee

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 (right-click 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.

Devin Lee

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 (right-click 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.