Calculate hours per agent in a certain statuses

Hello! I have an Active Projects sheet with columns "Owner", "Load" and "Status". I am trying to calculate how many hours (load) each agent has for projects in the "Development" or "Solutioning" status. Thank you for your assistance.

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    Per Owner you would need a formula that looks like this. You can also replace the text I have with an example name "Project Owner" with a cell reference if their name is found somewhere in the sheet.

    =SUMIFS(Load:Load, Owner:Owner, "Project Owner", Status:Status, OR(@cell = "Development", @cell = "Solutioning"))

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    Per Owner you would need a formula that looks like this. You can also replace the text I have with an example name "Project Owner" with a cell reference if their name is found somewhere in the sheet.

    =SUMIFS(Load:Load, Owner:Owner, "Project Owner", Status:Status, OR(@cell = "Development", @cell = "Solutioning"))

  • Catherine_Hernandez1
    edited 06/06/24

    @David Tutwiler Thank you so much! It seems I forgot an important bit of information, there are times there are more than one "owner", I tried adding HAS(OR(@cell) but getting an error.

    This is what I put

    =SUMIFS({Load}, {Owner}, HAS(@cell, "Catherine Hernandez"), {Status}, OR(@cell = "Development", @cell = "Solutioning"]))

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    HAS is more for evaluating ranges, I think you are looking for CONTAINS. A formula that looks like this should get you there:

    =SUMIFS(Load:Load, Owner:Owner, CONTAINS("Project Owner", @cell), Status:Status, OR(@cell = "Development", @cell = "Solutioning"))

  • @David Tutwiler we are so close lol. Ok I have =SUMIFS({Load}, {Owner}, CONTAINS("Catherine Hernandez", @cell), {Status}, OR(@cell = "Development", @cell = "Solutioning"))

    I get 0 as the result. my load column is a text/number field, if that matters. And of course using HAS only gives me an amount with my name only as the owner.

    Maybe I need to change this to an IF statement?

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Is the Owner field text/number? The name would have to exactly match Catherine Hernandez or will return a 0.

    If it is a contact field, there's a chance you might need to use the email address.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!