#### countryfan_nt

##### Well-known Member

- Joined
- May 19, 2004

- Messages
- 753

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!

Code:

`=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))))))))`