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

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!

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• ✭✭✭✭✭✭

Done, Please check the sheet you shared with me.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭

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.

bassam.khalil2009@gmail.com

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

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com

• edited 07/13/21

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

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• 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.

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• ✭✭✭✭✭✭
edited 07/14/21

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

bassam.khalil2009@gmail.com

• @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.

• ✭✭✭✭✭✭

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.khalil2009@gmail.com

• ✭✭✭✭✭✭

Done, Please check the sheet you shared with me.

bassam.khalil2009@gmail.com

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

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com