Help with multiple conditions formula

pete7722

New Member
Joined
Jul 18, 2012
Messages
7
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:

Code:
=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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 18, 2012
Messages
7
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

pete7722

New Member
Joined
Jul 18, 2012
Messages
7
Andrew,

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


SerNameDestinationStart TimeEnd TimeStart DateEnd DateCar ModelCar Registration
MumfordAndover10:0018:0026-Jul-1227-Jul-12Ford FiestaGK12 KYS
HayhurstMinley08:0020:0026-Jul-1226-Jul-12KIAGN61 0HV
WilliamsMinley08:0020:0026-Jul-1226-Jul-12Vauxhall Insignia HatchbackYY61 UVO
BurtonAndover08:0016:0026-Jul-1226-Jul-12Vauxhall Vectra SaloonOV07 CCX

<tbody>
</tbody>

Regards,

Pete
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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)),"")
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 18, 2012
Messages
7
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top