Solved

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

Posted on 2012-04-07
6
278 Views
Last Modified: 2012-04-09
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
0
Comment
Question by:BajanPaul
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

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

Expert Comment

by:Saqib Husain, Syed
ID: 37820629
Enter this in I2 and copy down

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

Author Comment

by:BajanPaul
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37822232
Try

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

Author Comment

by:BajanPaul
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

by:BajanPaul
ID: 37823659
Worked as described.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
second highest value difference 11 24
Prevent user inserting rows manually 28 46
formattig excel from access 3 18
onOpen 14 43
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now