Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-25-2011, 04:50 AM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 975
OTPM is on a distinguished road
Default Formula Help Required


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
File Type: xlsx Book3.xlsx (9.7 KB, 7 views)
Reply With Quote
  #2  
Old 08-25-2011, 08:03 AM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsx Book3.xlsx (11.3 KB, 6 views)
Reply With Quote
  #3  
Old 08-25-2011, 11:21 AM
Kimberly Kimberly is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: May 2010
Posts: 519
Kimberly is on a distinguished road
Default

=countifs($d$2:$d$10,d2,$c$2:$c$10,c2)
Reply With Quote
  #4  
Old 08-30-2011, 08:34 AM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 975
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #5  
Old 08-30-2011, 11:28 PM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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...
Reply With Quote
  #6  
Old 08-31-2011, 02:48 AM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 975
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #7  
Old 08-31-2011, 02:58 AM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum required without consider the negative amount PRADEEPB270 Excel 2 08-22-2011 04:23 AM
Help required with spacing rohanmalhotra Word VBA 3 08-11-2011 04:06 AM
Help required with mobile powerpoint scrichens PowerPoint 0 05-17-2011 02:58 AM
Suggestion required domex Word 0 10-06-2010 05:35 AM
Urgent Help Required id12345 Word 4 12-29-2009 08:22 AM


All times are GMT -7. The time now is 03:40 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft