Help with multiple conditions formula

[pete7722]

Hi all,

Im in need of some help with a multiple conditions formula. I have a vehicle booking workbook with a daily booking view. I am trying to break it down into part days but cannot get the conditions to meet.

The formula i am using is:

=IF(AND($B17<>"",$C17<>""),1-SUMPRODUCT(('Car Reservation'!$F$2:$F$1000<=E$15)*('Car Reservation'!$G$2:$G$1000>=E$15)*('Car Reservation'!$H$2:$H$1000=$B17)*('Car Reservation'!$I$2:$I$1000=$C17)*('Car Reservation'!$D$2:$D$1000<=E$16)*('Car Reservation'!$E$2:$E$1000>=E$16)),"")

F = Start Date
G = End Date
H = Car Make
I = Car Registration
D = Start Time
E = End Time

Thank you very much in advance for your help.

Regards,

Pete


I can get the formula to meet the conditions for the beginning time of each day but not to recognize the end time on a different day.

 

[Andrew Poulsom]

Welcome to MrExcel.

Is it?

=IF(AND($B17<>"",$C17<>""),1-SUMPRODUCT((('Car Reservation'!$F$2:$F$11+'Car Reservation'!$D$2:$D$11)<=(E$15+E$16))*(('Car Reservation'!$G$2:$G$11+'Car Reservation'!$E$2:$E$11)>=(E$15+E$16))*('Car Reservation'!$H$2:$H$11=$B17)*('Car Reservation'!$I$2:$I$11=$C17)),"")

 

[pete7722]

Andrew,

Thank you for your reply..... It is!!!!!!

Regards,

Pete

 

[pete7722]

Andrew,

I have another problem!!!

I am trying to link the name of the person (first 5 Characters) to the cell that shows they have booked the car. I have been able to do it for a single entry but cannot get it to look up the name against the date? The formula i am using is:

=IF(AND($B8<>"",$C8<>"")*(SUMPRODUCT(('Car Reservation'!$F$2:$F$999<=F$7)*('Car Reservation'!$G$2:$G$999>=F$7)*('Car Reservation'!$H$2:$H$999=$B8)*('Car Reservation'!$I$2:$I$999=$C8))),LEFT(TRIM('Car Reservation'!$B$2),5),2)

The code above works fine and inputs the name of the person from cell B2 into the correct date cell on the booking sheet and the cell turns red.

How do i change the The Bold, Underlined part to reference the cells B2:B999 and return the correct name?

Thank you in advance for your continued help,

Regards,

Pete

 

[Andrew Poulsom]

Can you post some sample data from worksheet Car Reservation please?

 

[pete7722]

Andrew,

Thank you for getting back to me the data below is taken from the car reservation sheet:

Ser	Name	Destination	Start Time	End Time	Start Date	End Date	Car Model	Car Registration
	Mumford	Andover	10:00	18:00	26-Jul-12	27-Jul-12	Ford Fiesta	GK12 KYS
	Hayhurst	Minley	08:00	20:00	26-Jul-12	26-Jul-12	KIA	GN61 0HV
	Williams	Minley	08:00	20:00	26-Jul-12	26-Jul-12	Vauxhall Insignia Hatchback	YY61 UVO
	Burton	Andover	08:00	16:00	26-Jul-12	26-Jul-12	Vauxhall Vectra Saloon	OV07 CCX

<tbody>
</tbody>

Regards,

Pete

 

[Andrew Poulsom]

Try:

=IF(AND($B17<>"",$C17<>""),INDEX('Car Reservation'!$B$2:$B$11,MATCH(1,INDEX((('Car Reservation'!$F$2:$F$11+'Car Reservation'!$D$2:$D$11)<=(E$15+E$16))*(('Car Reservation'!$G$2:$G$11+'Car Reservation'!$E$2:$E$11)>=(E$15+E$16))*('Car Reservation'!$H$2:$H$11=$B17)*('Car Reservation'!$I$2:$I$11=$C17),),FALSE)),"")

 

[pete7722]

It comes up with #N/A in the cell?

 

[Andrew Poulsom]

With your sample data and the following criteria:

B17 KIA
C17 GN61 0HV
E15 26-Jul-12
E16 10:00

the formula I posted returns Hayhurst as expected. It will return #N/A if there is no match.

 

[pete7722]

Andrew,

Thank you very much for all of your help. The formula works just fine (I copied it into the wrong table!!!!!)

Where would i add the LEFT/TRIM/6 part?