Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with more than one valid criteria
Hi,
Thanks in advance for any input you can give on this. I'm trying to organize a messy schedule, so I'm using the sumproduct function. The data available comes like this: Room StDate EndDate Day Outdoor=1/Indoor=2 1 5/30 7/15 M 1 3 6/2 8/3 TW 2 So I made a calendar for each room and I'm using this expression (sorry): =IF(SUMPRODUCT(--(Sheet1!$A$2:$A$300=Room),--(StDate=Sheet1!$C$2:$C$300),--(EndDate<=Sheet1!$D$2:$D$300),--(ISNUMBER(SEARCH(Day,Sheet1!$E$2:$E$300))))=1,"Out door",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$300=Room),--(StDate=Sheet1!$C$2:$C$300),--(EndDate<=Sheet1!$D$2:$D$300),--(ISNUMBER(SEARCH(Day,Sheet1!$E$2:$E$300))))=2,"Ind oor","")) PROBLEM: The problem is that if I have more than one event scheduled the same day, the formula goes with the second criteria, and if there is 3er event scheduled the same day, the day appears available. I.e.: If I have ONE event on 5/30, the formula will give "outdoor" or "indoor" (it works as it should be) If I have TWO events on 5/30, the formula will give "indoor" (the 2nd criteria, whether the event is outdoor or indoor) If I have THREE events on 5/30, the formula will give " " (like the day would be available, whether the event is outdoor or indoor) I would like that the formula stops if the first criteria is valid. Hope someone can help me. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with more than one valid criteria
Lilly,
You can use an array-entered formula (enter using Ctrl-Shift-Enter) like: =INDEX(F:F,MIN(IF(Sheet1!$A$2:$A$300=Room, IF(Sheet1!$C$2:$C$300<=StDate, IF(Sheet1!$D$2:$D$300=EndDate,IF(ISNUMBER(SEARCH( Day,Sheet1!$E$2:$E$300)),ROW($A$2:$A$300))))))) which will return 1 or 2 from the first matching value, or =INDEX(F:F,MAX(IF(Sheet1!$A$2:$A$300=Room, IF(Sheet1!$C$2:$C$300<=StDate, IF(Sheet1!$D$2:$D$300=EndDate,IF(ISNUMBER(SEARCH( Day,Sheet1!$E$2:$E$300)),ROW($A$2:$A$300))))))) which will return the 1 or 2 from the last matching value. Then you can use those cells to feed the IF( =1, "Indoor", "Outdoor") formula. HTH, Bernie MS Excel MVP "Lily" wrote in message ... Hi, Thanks in advance for any input you can give on this. I'm trying to organize a messy schedule, so I'm using the sumproduct function. The data available comes like this: Room StDate EndDate Day Outdoor=1/Indoor=2 1 5/30 7/15 M 1 3 6/2 8/3 TW 2 So I made a calendar for each room and I'm using this expression (sorry): =IF(SUMPRODUCT(--(Sheet1!$A$2:$A$300=Room),--(StDate=Sheet1!$C$2:$C$300),--(EndDate<=Sheet1!$D$2:$D$300),--(ISNUMBER(SEARCH(Day,Sheet1!$E$2:$E$300))))=1,"Out door",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$300=Room),--(StDate=Sheet1!$C$2:$C$300),--(EndDate<=Sheet1!$D$2:$D$300),--(ISNUMBER(SEARCH(Day,Sheet1!$E$2:$E$300))))=2,"Ind oor","")) PROBLEM: The problem is that if I have more than one event scheduled the same day, the formula goes with the second criteria, and if there is 3er event scheduled the same day, the day appears available. I.e.: If I have ONE event on 5/30, the formula will give "outdoor" or "indoor" (it works as it should be) If I have TWO events on 5/30, the formula will give "indoor" (the 2nd criteria, whether the event is outdoor or indoor) If I have THREE events on 5/30, the formula will give " " (like the day would be available, whether the event is outdoor or indoor) I would like that the formula stops if the first criteria is valid. Hope someone can help me. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with two valid criteria | Excel Worksheet Functions | |||
add two criteria that should not be met to sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT using more than 2 criteria? | Excel Worksheet Functions | |||
sumproduct and IF criteria?? | Excel Discussion (Misc queries) | |||
SUMPRODUCT with 3 criteria | Excel Discussion (Misc queries) |