Excel 2007/2010 Function to input value based upon multiple criteria

I want to use a function that will input the number 3 if a work center shift 1 and 2 occurs on the same from date. Also, If the condition is met, I only want to put it on the row where it first ocured.

Please see attached example worksheet.

+ I also have a part two that I need help with if any of you have conditional formatting experience.

Thanks in advance.

Paul
ExcelFunctionHelp.xlsx
BajanPaulAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Try

=IF(COUNTIFS(F2:$F$1000,F2,B2:$B$1000,B2)>1,3,"")
0
 
AnuroopsunddCommented:
didn't get your question properly.. can you explain what you actually want.
0
 
Saqib Husain, SyedEngineerCommented:
Enter this in I2 and copy down

=IF(COUNTIFS(F:F,F2,B:B,B2)>1,3,"")
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
BajanPaulAuthor Commented:
Is there anyway to not include duplicates within this function?  For instance, if I have the following date ranges:

WrkCntr  From Date    To date    Shift    DupShift
2500        5/18/12        5/19/12   1  
21300      6/4/12          6/4/12     1             3
21300      6/4/12          6/5/12     2

So the calendar will be setup: With Red color for 6/4/12 and then blue color for 6/5/12/  Notice Shift 1 and two are scheduled for the same day 6/4/12 only.  Please refer to the excel sheet                

=IF(COUNTIFS(F:F,F2,B:B,B2)>1,3,"")
0
 
BajanPaulAuthor Commented:
Worked perfectly.  Thanks.

Please don't forget there is a part two under the conditional formatting section.


Thanks again.
0
 
BajanPaulAuthor Commented:
Worked as described.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.