Project Resource View - Hours
Hi,
I am managing a project right now, and I want to see to how many hours per resource each time I assigned a tasks. The project that I am managing will run in 1 year so I want to capture and see like how many hours they spent to date.
In my sheet, I have an 'actual hours' column and 'assigned to' column (This is multiple contacts). I am documenting each time we have a meeting.
Is there a way that I can see the hours by using the Project Resource View? I try to use the project resource view but I can only see the percentage? Is there a way to see total hours per resource?
Appreciate your help on this!
Thanks,
Jerome
Best Answer
-
Hi Jerome,
No problem at all! Thanks for clarifying your ranges.
Copy/Paste this:
=SUMIF({NetSuite Project Plan & Asset List with Ga Range 2}, HAS(@cell, "Jerome Cababa"), {NetSuite Project Plan & Asset List with Ga Range 1})
In your formula, you have typed the range twice instead of using @cell. See that I replaced the entire Range 2 the second time with the @cell function.
Let me know if this works!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Jerome Cababa,
The resource viewer provides you with a view of % Allocation of resources so you can do resource management, balancing, etc.
Are you trying to determine how many hours are assigned to each resource so that you can determine if they are over allocated? If so, try using formulas on your 'actual hours' columns like SUMIFS where you can specify multiple criteria like Assigned To and perhaps a status column to only sum up tasks that are not completed.
Happy to help further if you can provide some examples and maybe screen shots.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Thanks @Ramzi - I tried to used the SUMIF but I am getting an error message.
So what I did is I created a new sheet to track all the hours per resource. On my reference sheet, I have the following columns:
Task Name, Status, Duration (Hours), Actual Hours , Assigned To, etc
We track all hours even all the meetings that we have. My "Assigned To" column has multiple contacts too.
I have attached the actual sheet that I am currently managing.
Here is my criteria:
My range is - Assigned To
Criterion - "Jerome Cababa" - I wanted to sum all my hours.
Sum_Range - Duration (Hours). I am not using the Actual Hours cause that's the actual hours spent for each resource spent in that meeting.
Formula - =SUMIF({NetSuite Project Plan & Asset List with Ga Range 5}, "Jerome Cababa", {NetSuite Project Plan & Asset List with Ga Range 5})
I am getting 0 on that formula. I should be getting 3.5
I tried used sumifs but getting an error.
Appreciate your help on this.
Thanks,
Jerome
-
Hi Jerome,
It looks like the formula is only calculating for when "Jerome Cababa" is the only selection in the drop-down list, versus being one of multiple selections. Try using the HAS function to search for a name:
=SUMIF({NetSuite Project Plan & Asset List with Ga Range 5}, HAS(@cell, "Jerome Cababa"), {NetSuite Project Plan & Asset List with Ga Range 5})
HAS is specifically designed for multi-select Drop Down columns (whether that's a List type of column or Contact type). The range in this instance is @cell, or to look in each cell of the previously stated range. Here's an article on HAS for more information.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks for the response Genevieve.
I tried the suggested formula and my total is still getting a Zero.
=SUMIF({NetSuite Project Plan & Asset List with Ga Range 2}, HAS({NetSuite Project Plan & Asset List with Ga Range 2}, "Jerome Cababa"), {NetSuite Project Plan & Asset List with Ga Range 1})
I tried to do SUMIFS
==SUMIFS({NetSuite Project Plan & Asset List with Ga Range 1}, {NetSuite Project Plan & Asset List with Ga Range 2}, HAS ({NetSuite Project Plan & Asset List with Ga Range 2},"Jerome Cababa"))
but result is #Unparseable
-
Hi Jerome,
You will want to state the column reference before the HAS, so the SUMIF knows what column to look at. Then inside of the HAS, use the @cell function - this tells the HAS to look within each cell of the previously stated column.
SUMIF works like this: Range, Criteria, Sum Range
So first you need to state your Range to search in (the column with the names).
Next you state your criteria, that it HAS "this name" in the cell (or @cell)
Finally, you state the Sum Range (the column with data to sum).
Which column is the one with the names, is it Range 2? Then is Range 5 the one with the data to sum? If so, try this:
=SUMIF({NetSuite Project Plan & Asset List with Ga Range 2}, HAS(@cell, "Jerome Cababa"), {NetSuite Project Plan & Asset List with Ga Range 5})
Or in other words:
=SUMIF({Column with Names}, HAS(@cell, "Jerome Cababa"), {Column with Hours})
Does that help?
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi, Genevieve.
Thanks for being patient with me. That's what I actually did.
Range is The column where I have my name on it which is the "Assigned To" Column. (This is the my Range 2)
Criterie is Put HAS @(cell, "Jerome Cababa"). (This is the my Range 2)
Sum Range is the Column with our Duration (Hours). (This is the my Range 1)
Here is the visual so you can see it.
Thanks, JEROME
-
Hi Jerome,
No problem at all! Thanks for clarifying your ranges.
Copy/Paste this:
=SUMIF({NetSuite Project Plan & Asset List with Ga Range 2}, HAS(@cell, "Jerome Cababa"), {NetSuite Project Plan & Asset List with Ga Range 1})
In your formula, you have typed the range twice instead of using @cell. See that I replaced the entire Range 2 the second time with the @cell function.
Let me know if this works!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Awesome! THANK YOU SO MUCH GENEVIEVE!!!
This works! I thought you are referring @cell to reference the Assigned To Column! I have been working this for 2 weeks now.
Thanks A LOT!
Jerome
-
Haha no worries at all!!!!
I'm so very glad that this worked for you!
Here's more information on the @cell function if you're interested.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives