SUMPRODUCT with two valid criteria

[Lily]

Hi, Thanks in advance for your help.

I am creating a calendar to see time availability from a huge table that
list the room, the date, the day, and the time. We rent our facilities, so I
need to know which events are ours and which one are for outsider.
PROBLEM: I found out that if there is an event planned at the same time in
the same day, the formula retrieves a blank space, so it shows as available.
I'm awared that this is a problem of my database, but is there a way than
once the criteria finds one slot occupied, ignore the other events duplicated?

This is the expression (Sorry!):

=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$2500=Room),--(BegDate>=Sheet1!$C$2:$C$2500),--(EndDate<=Sheet1!$D$2:$D$2500),--(Day=Sheet1!$E$2:$E$2500))),--(Time=Sheet1!$G$2:$G$2500),--(Sheet1!$I$2:$I$2500))=1,"Company",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$2500=Room),--(BegDate>=Sheet1!$C$2:$C$2500),--(EndDate<=Sheet1!$D$2:$D$2500),--(Day=Sheet1!$E$2:$E$2500))),--(Time=Sheet1!$G$2:$G$2500),--(Sheet1!$I$2:$I$2500))=2,"Outside",""))

My calendar looks like this:
"Room" 4/5 4/6 4/7 4/8
4/9.............
M T W H
F................
Morning
Afternoon


THANKS MUCH!

 

[T. Valko]

This is the expression

I'm not sure what you're asking but that formula can be reduced a bit. It
looks like all you're interested in is a result of either 1 or 2. Anything
else returns a blank.

You don't need to use both SUMPRODUCTs.

All on one line:

=LOOKUP(SUMPRODUCT(
--(Sheet1!$A$2:$A$2500=Room),
--(BegDate>=Sheet1!$C$2:$C$2500),
--(EndDate<=Sheet1!$D$2:$D$2500),
--(Day=Sheet1!$E$2:$E$2500),
--(Time=Sheet1!$G$2:$G$2500),
Sheet1!$I$2:$I$2500),{0;1;2;3},
{"";"Company";"Outside";""})