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 |
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 |
All times are GMT +1. The time now is 06:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com