How to sum total meeting hours members of a project will have to attend?

jimf

I have a Smartsheet that contains the meeting name, the number of hours the meeting will be conducted, and a contact list of the attendees of each meeting for a project. I would like to create a formula that will sum the total meeting hours each member of the project team will be expected to attend.

For example, the formula for the above should result in the following:

Person A = 2.5 hrs

Person B = 3 hrs

Person C = 3 hrs

Person D = 2 hrs

....

I believe the solution, not sure though, is to use a metric report that lists each person in the project in one column. Then use the SUMIFS formula to provide the total hours of meetings that are calculated from the table above.

In the metrics report I have the names of the team members in one column:

Person A

Person B

Person C

In the next column over I have the formula which uses the name as the target. It seems to calculate zero for all the names listed. I'm wondering if when SUMIFS is calculating the total hours it is not finding the name match in the list even though the name is in the list. Any help would be greatly appreciated! Thank you in advance

Mike TV

@jimf

Person A formula:

=SUMIF([Attendee List (Contact List)]:[Attendee List (Contact List)], CONTAINS("Person A", @cell), [Hours (hrs)]:[Hours (hrs)])

jimf

The result was 0 for each Attendee:

The formula was not exactly like the above suggested since it is on another page needing to be referenced:

SUMIF({Kickoff Schedule Range 3}, CONTAINS([Primary Column]@row, {Kickoff Schedule Range 2})) where

Kickoff Schedule Range 3 is the attendee list referenced on another sheet and Kickoff Schedule Range 2 is the hrs referenced on another sheet.

Primary Column@row is the Name of the person on the metrics sheet where I'm trying to calculate the total hours.

Mike TV

@jimf

You changed it considerably. This should be a working version based on the ranges you're using:

=SUMIF({Kickoff Schedule Range 3}, CONTAINS([Primary Column]@row, @cell), {Kickoff Schedule Range 2}))

jimf

Thank you. I did try this but I get an #UNPARSEABLE error....weird.

jimf

I think there maybe an extra parens at the end of the formula provided. Once I removed it, the #UNPAREABLE error stopped occurring. I still get a 0 result.