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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!