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
-
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
-
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 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"]))
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!