Sumproduct with more than one valid criteria

[Lily]

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,"Outdoor",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,"Indoor",""))

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

 

[Bernie Deitrick]

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