How to calculate total hours of work per month for each resource

Ingrid Onguene

Hi all, l am trying to sum the total number of hours each person within our team has worked across different contracts. However l am not sure the correct formula to use for this? I believe if would be a SUMIF but every formula l attempt either brings it back as invalid or unparseable. I have attached a screenshot the smartsheet I am working on. Resource 1 is the column that l would get the name of the resource from, and the date columns ie 01/03/2021 are where their hours are inputted into for that month.

James Keuning

You need to normalize your data. You should never use columns as variables.* This can be confusing the first time you are confronted with it, but your data should look like this:

Once you make that change, you can use SUM and COLLECT. Pivot the data so that the table is normalized, and then try to use COLLECT. Follow-up here if you still can't figure it out.

EDIT: the advice to NEVER do a thing is often overblown, but smarter people than me gave me that advice and it's worked our fairly well. The column name should not be data.

Ingrid Onguene

https://community.smartsheet.com/discussion/comment/285614#Comment_285614

Hi James, this is helpful to know for future but this could be time consuming as each resource have about around 40+ hours split across 10+ contracts per month, so l would need to use the February date about 40 times and so forth and it would get quite lengthy hence l used them as headers. Is there no quicker way to display this? or a way to rename to columns?

James Keuning

So this table is just going to keep getting more and more columns?

I would approach this by creating a new sheet, with one record for each person/project/month combination and then use SUM and COLLECT, but since I do not work with data configured this way, I don't have direct advice.