Options
Jerome Cababa ✭✭
05/19/20 in Smartsheet Basics
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
0 · Share on FacebookShare on Twitter
Genevieve P. Employee
06/17/20 Answer ✓
Options
Hi Jerome,
No problem at all! Thanks for clarifying your ranges.
Copy/Paste this:
=SUMIF({NetSuite Project Plan & Asset List with GaRange 2}, HAS(@cell, "Jerome Cababa"), {NetSuite Project Plan & Asset List with GaRange 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 now5 · Share on FacebookShare on Twitter
Answers
Ramzi K ✭✭✭✭✭
05/25/20
Options
Hi @Jerome Cababa,
The resource viewer provides you with a view of % Allocation of resources so you can do resource management, balancing, etc.
See AlsoUltimate Guide to Resource Planning | SmartsheetProject Portfolio Management Guide | SmartsheetResource availability | Smartsheet Learning CenterCreate projects in Resource ManagementAre 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 bymarking it as the accepted answer/helpful.
0 · Share on FacebookShare on Twitter
Jerome Cababa ✭✭
05/25/20
Options
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
0 · Share on FacebookShare on Twitter
Jerome Cababa ✭✭
06/05/20
Options
UP
0 · Share on FacebookShare on Twitter
Genevieve P. Employee
06/11/20 edited 06/11/20
Options
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 now0 · Share on FacebookShare on Twitter
Jerome Cababa ✭✭
06/17/20
Options
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
0 · Share on FacebookShare on Twitter
Genevieve P. Employee
06/17/20
Options
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 now0 · Share on FacebookShare on Twitter
Jerome Cababa ✭✭
06/17/20
Options
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
0 · Share on FacebookShare on Twitter
Genevieve P. Employee
06/17/20 Answer ✓
Options
Hi Jerome,
No problem at all! Thanks for clarifying your ranges.
Copy/Paste this:
=SUMIF({NetSuite Project Plan & Asset List with GaRange 2}, HAS(@cell, "Jerome Cababa"), {NetSuite Project Plan & Asset List with GaRange 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 now5 · Share on FacebookShare on Twitter
Jerome Cababa ✭✭
06/17/20
Options
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
0 · Share on FacebookShare on Twitter
Genevieve P. Employee
06/17/20
Options
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 now0 · Share on FacebookShare on Twitter
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 60.1K Get Help
- 118 Global Discussions
- 107 Industry Talk
- 404 Announcements
- 13 Community Corner Newsletter
- 73 Brandfolder
- 125 Just for fun
- 49 Community Job Board
- 23 Member Spotlight
- 1 SmartStories
- 261 Events
- 21 Webinars
- 7.3K Forum Archives
Watch video
