formulas placed in a cell - showing different results based on multi conditions.

[countryfan_nt]

Hello friends, hope you are all well!
The calendar attachment here has 2 sheets: 1. “Availability”. And 2. “Car Reservation”.

I want to place formulas in sheet “Availability” cells F14:T16. The result of the formula will show a number between 1 to 9, based on the results. And the conditional formatting will color the cell based on the results (between 1 to 9).
OK the formula will:
a. Will go to 'Car Reservation' and look in $F$2:$F$995 and if it is <= than date in “Availability” F$12.
b. Will go to 'Car Reservation' and look in $G$2:$G$995 and if it is >= than date in “Availability” F$12.
c. Will go to 'Car Reservation' and look in !$H$2:$H$995 and if it = $C14 in sheet “Availability”.
d. And if 'Car Reservation' and look in $i$2:$i$995 = VACATION & COMITEE, then the result will be 7.

And so on, like the 'row 3' results and conditions of sheet “Availability”. so the cell will have

I really appreciate your kind help and thank you very much in advance!

[hemesh]

try using

Please Login or Register  to view this content.

you could use sumifs too instead of sumpropduct

[countryfan_nt]

Hi! and thanks a lot! I made some adjustments, but now facing a new related problem: the formula is in F14:

{=IF(SUM((('Car Reservation'!$H$2:$H$2000=C14)*('Car Reservation'!$F$2:$F$2000<=F$12)*('Car Reservation'!$G$2:$G$2000>=F$12)*('Car Reservation'!$I$2:$I$2000={"vacation","Comitee","Career"})))>=3,7,IF(SUM((('Car Reservation'!$H$2:$H$2000=C14)*('Car Reservation'!$F$2:$F$2000<=F$12)*('Car Reservation'!$G$2:$G$2000>=F$12)*('Car Reservation'!$I$2:$I$2000={"comitee","Career"})))>=2,6,IF(SUM((('Car Reservation'!$H$2:$H$2000=C14)*('Car Reservation'!$F$2:$F$2000<=F$12)*('Car Reservation'!$G$2:$G$2000>=F$12)*('Car Reservation'!$I$2:$I$2000={"comitee","vacation"})))>=2,5,IF(SUM((('Car Reservation'!$H$2:$H$2000=C14)*('Car Reservation'!$F$2:$F$2000<=F$12)*('Car Reservation'!$G$2:$G$2000>=F$12)*('Car Reservation'!$I$2:$I$2000={"vacation","Career"})))>=2,4,1))))}

The result that I am getting is ‘7’ which is wrong, please look at the data base ('Car Reservation') you will see that {"vacation","Comitee","Career"} do not exist. So why get 7?

Your time and efforts are truly appreciated!

[hemesh]

{"vacation","Comitee","Career"}

Above stands for any out of these
in your data Comitee and career stands for 3 times so you are getting that results.

[countryfan_nt]

great, is there a way to place the above three in a AND formula within that formula please?

[hemesh]

you could use something like below

=SUMPRODUCT(--(COUNTIFS('Car Reservation'!$H$2:$H$2000,C14,'Car Reservation'!$F$2:$F$2000,"<="&F$12,'Car Reservation'!$G$2:$G$2000,">="&F$12,'Car Reservation'!$I$2:$I$2000,{"vacation","Comitee","Career"})>=1))

above will give you 2 as career and comitee is a match

[countryfan_nt]

thank you for the reply, I actually want to show a different color if there is conflicting scheduling, that is why I am wondering if you can help me create an AND function within the formula please?
i.e. if the database all conditions are met column I (TYPE) has a triple booking of a vacation, Career, and Comitee; then I display a different color to show that there is a conflict.

[hemesh]

with above formula, I think you can adjust all your conditions

[hemesh]

=SUMPRODUCT(--(COUNTIFS('Car Reservation'!$H$2:$H$2000,C14,'Car Reservation'!$F$2:$F$2000,"<="&F$12,'Car Reservation'!$G$2:$G$2000,">="&F$12,'Car Reservation'!$I$2:$I$2000,{"vacation","Comitee","Career"})>=1))=3

change 3 to 2 to check if its meeting two conditions

[countryfan_nt]

very cool! thanks!

[hemesh]

if that takes care of your original question please go to thread tool and mark thread as solved