Project Resource View - Hours (2024)

Answers

• Ramzi K ✭✭✭✭✭

  05/25/20

  Options

  Hi @Jerome Cababa,

  See Also

  Ultimate Guide to Resource Planning | SmartsheetSync Resource Management assignments with SmartsheetResource Management 101| Smartsheet

  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 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 Admin

  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

  0 · 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 Admin

  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

  0 · 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 Admin

  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!

  5 · 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 Admin

  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.

  0 · Share on FacebookShare on Twitter