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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!