Thread: Creating a nested SUMPRODUCT function that will bring back up to 4 different results

Hi,

I recently posted a problem about creating a function for an interactive booking/reservation schedule for an aquatics community center. This is the function that was written for me by an extremely helpful poster called Haseeb:

=(SUMPRODUCT(--(ConferenceRooms!$D$2:$D$100=LOOKUP(9E300,$B$5:B$5)),--(ConferenceRooms!$G$2:$G$100=B$6),--(ConferenceRooms!$F$2:$F$100>=$A7),--(ConferenceRooms!$E$2:$E$100<=$A7+"00:29"))=0)+0

This code basically took four different variables (Date, Start Time, End Time, Room Type) on one worksheet and linked them to the schedule on another worksheet. Based on the information, it would return a value of 1 or 0, 1 meaning the specific room was available at this time at this date and 0 meaning that it had been booked.

I have two questions as follows(The FIRST question/problem is more important in the sense that that is the feature that I REALLY need. If you can also answer the second question, then you would more than make my day, but the first is what I really need):

1.

I've added another variable column to my information worksheets called TENTATIVE. I want to include this in my SUMPRODUCT function so that when the value in this column is equal to "YES", the function will compute the information along with all the other information to return a value of 2 (or any other acceptable number as long as it is different from 0,1,) The objective of this is to be able to then conditionally format the cells with this value to a separate color so that it represents a TENTATIVE booking.

2.

The original template that I based my design on used the the SUMPRODUCT function nested in an IF function to do what the formula above does for me. However, the main difference is that the nested IF function would automatically return a value of -1 if a room was double booked (i.e same dates/ times) while the one that I am using right now DOESN'T. I was wondering if it would be possible to do this with the function I'm using now (above)?

Attached below is the said Workbook. You will notice that there are multiple schedules. They all use the same basic SUMPRODUCT function that I posted above, with slight variations to match each schedule. If you're playing around with fixing the problem, you can do it just for one schedule because I can adapt it to suit the others usually without problem.

I tried playing with the function and my final failed brain wave for the first problem was the following:

IF(AND((SUMPRODUCT(--(ConferenceRooms!$E$2:$E$100=LOOKUP(9E+300,$B$5:G$5)),--(ConferenceRooms!$H$2:$H$100=G$6),--(ConferenceRooms!$G$2:$G$100>=$A10),--(ConferenceRooms!$F$2:$F$100<=$A10+"00:29"))=0)+0,'ConferenceRooms'!$D$2:$D$100="Yes"),2,((SUMPRODUCT(--(ConferenceRooms!$E$2:$E$100=LOOKUP(9E+300,$B$5:G$5)),--(ConferenceRooms!$H$2:$H$100=G$6),--(ConferenceRooms!$G$2:$G$100>=$A10),--(ConferenceRooms!$F$2:$F$100<=$A10+"00:29"))=0)+0))

It did not work. If someone could please help, I would really really appreciate it!
Hope everyone had a good day today.

Sam

For #1, assuming you do use Excel 2010 as stated...

=IF(COUNTIFS(ConferenceRooms!$E$2:$E$100,LOOKUP(9.99E+300,$B$5:B$5),ConferenceRooms!$H$2:$H$100,B$6,ConferenceRooms!$D$2:$D$100,"Yes",ConferenceRooms!$G$2:$G$100,">="&$A7,ConferenceRooms!$F$2:$F$100,"<="&$A7+"00:29")=0,2,IF(COUNTIFS(ConferenceRooms!$E$2:$E$100,LOOKUP(9.99E+300,$B$5:B$5),ConferenceRooms!$H$2:$H$100,B$6,ConferenceRooms!$G$2:$G$100,">="&$A7,ConferenceRooms!$F$2:$F$100,"<="&$A7+"00:29")=0,1,0))

For No. 2, you need to state the priority sequence.. do you need to know first if the row is Tentatively assigned? Is 1 tentative and one not tentative considered double-booked, etc..

Perhaps I misunderstand your requirement. If you put Yes's in all the cells of the Tentative column you will see 0's appear in those date ranges.... Is that incorrect? What exactly are you expected when there are Yes's and when there are not any Yes's in the Tentative column cells?

I think we just need to change the first condition to check if count is greater than 0, instead of equalling 0.

=IF(COUNTIFS(ConferenceRooms!$E$2:$E$100,LOOKUP(9.99E+300,$B$5:B$5),ConferenceRooms!$H$2:$H$100,B$6,ConferenceRooms!$D$2:$D$100,"Yes",ConferenceRooms!$G$2:$G$100,">="&$A7,ConferenceRooms!$F$2:$F$100,"<="&$A7+"00:29")>0,2,IF(COUNTIFS(ConferenceRooms!$E$2:$E$100,LOOKUP(9.99E+300,$B$5:B$5),ConferenceRooms!$H$2:$H$100,B$6,ConferenceRooms!$G$2:$G$100,">="&$A7,ConferenceRooms!$F$2:$F$100,"<="&$A7+"00:29")=0,1,0))