How do I SUMIF based on multiple parameters?

Karla George

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!

Karla George

This worked! THank you so much for the fast and helpful response!

Paul Newcome

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())

Karla George

This worked! THank you so much for the fast and helpful response!

Paul Newcome

Happy to help. 👍️