SumIFs With AND not OR

[countryfan_nt]

Hello friends, Hope all is well! I need your kind help please.

I have the below formula, and I want it to work like sumifs with AND statements; example:

I want the formula to show a result if the formula finds BOTH words (all or nothing): "vacation" AND "Career". If not, then zero.

{=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"}))), ....


thank you in advance!

All the best!

=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"})))>=2,3,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,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={"comitee","career","vacation"})))>=1,2,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={"career"})))>=2,2,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"})))>=2,2,1))))))))

 

[steve the fish]

It will be something like this. Add your sheet names:

=COUNTIFS($H$2:$H$2000,C14,$F$2:$F$2000,"<="&F$12,$G$2:$G$2000,"<="&F$12,$I$2:$I$2000,"*vacation*",$I$2:$I$2000,"*career*")

 

[countryfan_nt]

thank you! can you please add a IF statement, so that I can make multi scenarios? thanks again!

 

[steve the fish]

No idea what you mean im afraid

 

[countryfan_nt]

sorry for not being clear. if you see the original post you will see that there are several if statements. I wish to have an =if( added to the formula, and a result of the IF statement (please).

 

[steve the fish]

=IF(COUNTIFS($H$2:$H$2000,C14,$F$2:$F$2000,"<="&F$12,$G$2:$G$2000,"<="&F$12,$I$2:$I$2000,"*vacation*",$I$2:$I$2000,"*career*"),TRUE,FALSE)