Sumproduct with more than one valid criteria

L

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top