Bugs: SUMIFS does not find multiple users



  • Jennifer Jones
    edited 01/03/19

    I use the [Assigned To] column along with an Index formula to find the correct charge rate for a resource and then use a multiplier to calculate Cost to Complete for a task (which rolls up to the the project).

    The formula uses the contact in the [Assigned To] column, and index lookup for the cost rate multiplier, the [% Complete], [% Allocation] and the [Duration] in order to calculate the Cost to Complete for the project. This rolls up and informs all of our project dashboards.

    I was looking forward to the multiple Assign To capability, but I can't use it. Even if I assume that each resource is equally Allocated, I can't look up the rates for individuals, or even automatically count how many resources are in the box and then estimate using a 'blended rate'.

    Any ideas anyone? 

  • Has this been fixed?  I am trying to write a formula to count the number of assignments a contact has where the assignment column is a multiselect contact column.

  • Daniel,

    I am using a formula similar to the one you provide above


    Row 10: =SUMIF([Assigned To]:[Assigned To], FIND("Emily Young", @cell) > 0, Hours:Hours)

    This formula works, however if I try to use it in the row below for a different name

    Row 11: =SUMIF([Assigned To]:[Assigned To], FIND("Timothy Lawrence", @cell) > 0, Hours:Hours)

    Then Row 10 shows #CIRCULAR and Row 11 shows #BLOCKED and I'm not sure why. I need a total amount of hours for each employee on a project.

  • Was tlawrence17's question on 9/20/19 ever answered? I'm running into the same problem.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @tlawrence17 and @DeeL

    Are you putting the formula in one of the referenced columns?