Calculate hours per agent in a certain statuses

Catherine_Hernandez1

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.

David Tutwiler

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"))

David Tutwiler

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

@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

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"))

Catherine_Hernandez1

@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

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.