Cross-Referencing Assigned To Error

craig0220

I am building out a roll-up sheet and have everything working nicely, with the exception of a single formula. The part of this sheet I'm having trouble with is in the image below. What I am trying to do is have this sheet look at the person named in the column "Primary", then check a couple other sheets to see how many tasks that person is assigned to, and then output the number of tasks that have been completed and the number remaining to-do.

I am currently doing this with the formula:

=COUNTIFS({Assigned To}, Primary7, {Status}, <>"Complete") + COUNTIFS({Assigned To 2}, Primary7, {Status 2}, <>"Complete")

As you can see in the screenshot, I am getting an output from these, but it isn't correct. Right now, this formula is accounting for only the tasks assigned to a single person.

I have tried numerous ways to incorporate the CONTAINS() variable to loosen that restriction, but I'm getting formula errors. Any suggestions on the best way for me to modify this formula to achieve the goal?

Paul Newcome

Is the {Assigned} range a multi-select contact type column? If so, you will need the HAS function.