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

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    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

  • Genevieve P.
    Genevieve P. Employee
    edited 06/11/20

    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

  • 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