Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2012-04-07
Medium Priority
325 Views
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.

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

Paul
ExcelFunctionHelp.xlsx
0
Question by:BajanPaul
• 3
• 2

LVL 17

Expert Comment

ID: 37820598
didn't get your question properly.. can you explain what you actually want.
0

LVL 43

Expert Comment

ID: 37820629
Enter this in I2 and copy down

=IF(COUNTIFS(F:F,F2,B:B,B2)>1,3,"")
0

Author Comment

ID: 37822193
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

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 37822232
Try

=IF(COUNTIFS(F2:\$F\$1000,F2,B2:\$B\$1000,B2)>1,3,"")
0

Author Comment

ID: 37823654
Worked perfectly.  Thanks.

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

Thanks again.
0

Author Closing Comment

ID: 37823659
Worked as described.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URLâ€™s and adds a header titled â€śURL Listâ€ť. It then searches through all URLâ€™s in column â€śAâ€ť, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URLâ€™s are then highligâ€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month9 days, 11 hours left to enroll