Hi All



I am looking for help to produce a formula that will tell me if the same person is assigned to work on two projects at the same time. A sample worksheet is enclosed.

Thanks for anyones help.

Tony (OTPM)

Attached Files

Book3.xlsx (9.7 KB, 13 views)

well, this could be an option...
=IF(K4>0;COUNTIFS(C$2:C$10;C4;K$2:K$10;">0";H$2:H$10;H4);IF(L4>0;COUNTIFS(C$2:C$10;C4;L$2:L$10;">0";H$2:H$10;H4);""))

but it starts counting if the project name, task name and start date are the same; if you do not want to take into accout the start date, just remove the red refferences from formula..
there are 2 alternatives, in sheet 1 and sheet 2, attached.

Attached Files

Book3.xlsx (11.3 KB, 12 views)

=countifs($d$2:$d$10,d2,$c$2:$c$10,c2)

Hi Both
Sorry for the delay in responding. Thanks for the suggestions so far however it does not give me what I need. Perhaps I did not explain clearly enough.
Basically I need to return an answer of "Yes" if the same resource is assigned to tasks on different projects (Column C) during the same time periods Columns H and I.
Hope this makes it clearer.
Kind regards
Tony(OTPM)

I think this will do what you need:

=IF(COUNTIFS($D$2:$D$10;D2;H$2:H$10;H2;I$2:I$10;I2 )>COUNTIFS(D$2:$D$10;D2;C$2:C$10;C2;H$2:H$10;H2;I$ 2:I$10;I2);"Yes";"")
but you will get an Yes for duplicate entries too... Anyway, I guess in the real table there will be no duplicates (or you will use data instruments-remove duplicates). If there will be duplicate entries, then we need to extend the formula...

Hi Catalin
Many thanks for the formula, all I had to change was all the semi-colons to commas and it worked fine.
If only internal departments in my company could work as efficiently and effectively as this forum does. Aaaahhhh we can only dream can't we.
Many thanks for your help.
Tony(OTPM)

Glad it worked...
Well, i have a topic too, but still no answer yet...maybe the problem it's too difficult