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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!