How can I calculate total hours per person for a project?

Lisa Hunt

My projects are set up with a column for number of hours per person for each subtask, and then I use a formula to calculate %allocation for each person. That is working fine for looking at the percent of time each person is allocated across projects. However, is there a way to see the total number of hours for each person for the whole project (summed across all tasks)? Thanks!

Bassam Khalil

Hi @Lisa Hunt 

Please add a new column call it "SUM" and apply the following formula in this column and convert it to column format formula;

=IFERROR(IF(Hours@row = 0, "", SUMIFS(Hours:Hours, [Assigned To]:[Assigned To], [Assigned To]@row)), "")

the following screenshot shows the result:

Bassam Khalil

@Lisa Hunt

Done, Please check the sheet you shared with me.

Bassam Khalil

Hi @Lisa Hunt 

Hope you are fine, you can use SUMIFS function to do that, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Lisa Hunt

Thanks @Bassam Khalil . But I am having trouble getting the syntax right for that function. Attached is a copy of my sheet.

DRMP Modeling July-Aug 2021 (1).pdf

Bassam Khalil

Hi@Lisa Hunt

You need the formula in same sheet or in new summary sheet?

Lisa Hunt

@Bassam Khalil I would prefer to have it in the same sheet.

Bassam Khalil

Hi @Lisa Hunt 

Please add a new column call it "SUM" and apply the following formula in this column and convert it to column format formula;

=IFERROR(IF(Hours@row = 0, "", SUMIFS(Hours:Hours, [Assigned To]:[Assigned To], [Assigned To]@row)), "")

the following screenshot shows the result:

Lisa Hunt

Hi @Bassam Khalil this is super helpful, thank you! It almost works but not quite because my projects have some rows where there are multiple people assigned, and this formula doesn't include the hours in those rows when summing hours for each person. Not sure if there is any way to make it work for that though - I might just have to use separate rows for each person rather than having multiple people in the "Assigned To" column.

Bassam Khalil

Hi @Lisa Hunt

Yes we can calculate for multi assigned people by using Contain or HAS function for the criteria, i will create the formula for you tomorrow morning.

Bassam Khalil

Hi @Lisa Hunt

please try the following formula, but please note that you need to create a list of all of your users and apply the formula for the list as following:

=IFERROR(SUMIFS(Hours:Hours, [Assigned To]:[Assigned To], CONTAINS(USER@row, @cell)), "")

Or the following formula using HAS function

=IFERROR(SUMIFS(Hours:Hours, [Assigned To]:[Assigned To], HAS(@cell, USER@row)), "")

the following screenshot shows the result:

@Lisa Hunt

Lisa Hunt

@Bassam Khalil thanks so much for helping me troubleshoot this. When I use the formula with "CONTAINS", it doesn't seem to work at all because it returns a value of zero. When I use the formula with "HAS" it is counting the hours for the rows that just contain one person in the "Assigned To" column, but not the hours for the rows that contain more than one person in the "Assigned To" column. So basically does the same thing as the previous formula. I guess I will just have to put hours for each person on a separate row in order for this to work.

Bassam Khalil

@Lisa Hunt

Could you please share me as an admin on a copy of your sheet after removing any sensitive data, so I can solve this problem for you. Because as you see I tested this formula and it's working ( check the screenshot).

Bassam Khalil

@Lisa Hunt

Done, Please check the sheet you shared with me.

Lisa Hunt

Thank you so much Bassam - you have been extremely helpful and I really appreciate you taking the time to help me figure this out!

Bassam Khalil

@Lisa Hunt

Excellent, I will be happy to help you any time, I will be grateful for your vote on the post.

Misty

@Bassam Khalil Hi! I'm having the same issue as Lisa, and I tried your 7/14/21 solution: =IFERROR(SUMIFS(Hours:Hours, [Assigned To]:[Assigned To], CONTAINS(USER@row, @cell)), ""). I've figured out that the problem is my "Assigned To" contains a first and last name, and I cannot figure out how to properly add the brackets to the formula so that it works. To test this I added users with a single name "Smurf" and "Peanut" and the formula is working for them.