Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with two valid criteria
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with two valid criteria
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";""}) -- Biff Microsoft Excel MVP "Lily" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with 3 criteria | Excel Discussion (Misc queries) | |||
Sumproduct criteria | Excel Worksheet Functions | |||
SUMPRODUCT using more than 2 criteria? | Excel Worksheet Functions | |||
SUMPRODUCT using more than 2 criteria? | Excel Worksheet Functions | |||
SUMPRODUCT with 3 criteria | Excel Discussion (Misc queries) |