How do I SUMIF based on multiple parameters?
I am trying to calculate employee utilization rates in smartsheet. I want to know the sum of individual employee effort (in percentage form) at any given given date in time. I have a list of current projects that has a line item for each employee and their percent effort on the project. Each line also indicates the start/end date of the project. Right now I'm half way there using this formula to sum effort across all awarded projects by each employee:
=SUMIF({1. Awarded Projects Range 1}, "Employee Name", {1. Awarded Projects Range 2})
I am missing the ability to distinguish effort as of a given date and so the formula is picking up all effort for all awarded projects across time. This presents a problem because for some employees i am showing that they are 200% allocated since my formula does not consider that the employee is allocated 100% for one week and 100% for another. It is summing everything up into one number.
I have contemplated adding the "TODAY()" function into my sheet and creating a new column/formula that returns the number "1" if "Today" is between the project start and end dates, thinking that any non active work would return a zero. I could then fold this into my formula above with the thinking that if there is a number "1" then that line would be added to the total effort and if there is a "0" that line would not be added.
Its a little confusing, i've been at this for a few hours, any help would be greatly appreciated!
Thank you!
Best Answer
-
This worked! THank you so much for the fast and helpful response!
Answers
-
Try switching over to a SUMIFS which will allow you to include additional ranges and criteria which you can use to narrow things down (note the different location for the range to sum).
=SUMIFS({1. Awarded Projects Range 2}, {1. Awarded Projects Range 1}, "Employee Name", {1. Awarded Projects Start Date Column}, @cell <= TODAY(), {1. Awarded Projects End Date Column}, @cell >= TODAY())
-
This worked! THank you so much for the fast and helpful response!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!