Multiple Contacts and effort total

jwoods

Hello,

I am tracking tasks with an Assigned To column and Effort column. My original sheet required repeating the same task and assigning to each individual who would perform that task, and then using the Sum(Children()) to get the total effort for all child tasks.

I would like to allow the Assigned To column to use multiple contacts, with effort set to the amount per user, and then sum up the row.

For example

Task | Assigned To | Effort

Task1 | user1, user2, user3 | 1

Task2 | user2, user3 | 2

I would expect the total effort to be (3*1) + (2*2) = 7

I haven't figured out a formula that would give me total effort of child tasks with multiple contacts. Any ideas?

Genevieve P.

Hi @jwoods

Since your effort is different per-row, the way I would do this is to set up another helper column in order to do the initial calculation of number of users * effort.

In this column I'd use the COUNTM function to identify the number of users selected, then multiply this by the effort. Try this:

=COUNTM([Assigned To]@row) * Effort@row

You can make this a column formula, perhaps wrapping an IFERROR around it:

=IFERROR(COUNTM([Assigned To]@row) * Effort@row, "")

Then in your SUM(CHILDREN()) function you can reference the helper column inside the CHILDREN function so it looks at the actual effort across resources:

=SUM(CHILDREN([Helper Column]@row))

Cheers!

Genevieve