MULTIPLE IF FORMULA

[kumara_faith]

Hi,

I have the following table:

COLUMN A COLUMN B COLUMN C

BRANCH	INFO COUNTER	% OF CUSTOMERS MANAGED AT INFO COUNTER FROM THE TOTAL TICKETS PER DAY	MESSAGE
TEXAS

I have the following criteria:

1) If column B is and column C is empty, then return as "Please complete column B and C"
2) If column B is empty and column C is filled, return as "Please complete column B"
3) If column C is empty and column B is filled, return as "Please complete column C"
4) If column B is filled with NO but column C has a number, return as "Column B is filled as NO but column C has a number, check again"
5) If column B is filled as YES but column C is filled with 0, then return as "Column B is filled as YES but column C is filled with ZERO, check again"
6) Otherwise return as COMPLETE

I tried with the following formula but it does not seem to work:

=IF(AND([@[INFO COUNTER]]="",[@[% OF CUSTOMERS MANAGED AT INFO COUNTER FROM THE TOTAL TICKETS PER DAY]]=""),"PLEASE COMPLETE COLUMN C AND D",IF(AND([@[INFO COUNTER]]="",[@[% OF CUSTOMERS MANAGED AT INFO COUNTER FROM THE TOTAL TICKETS PER DAY]]<>""),"PLEASE COMPLETE COLUMN C",IF(AND([@[INFO COUNTER]]<>"",[@[% OF CUSTOMERS MANAGED AT INFO COUNTER FROM THE TOTAL TICKETS PER DAY]]=""),"PLEASE COMPLETE COLUMN D",IF([@[INFO COUNTER]]="NO",[@[% OF CUSTOMERS MANAGED AT INFO COUNTER FROM THE TOTAL TICKETS PER DAY]]<>""),"INFO COUNTER WAS SELECTED AS NO BUT COLUMN D HAS A %, PLEASE CHECK AGAIN")""))

Appreciate any help.

 

[Sufiyan97]

Try

=IF(AND(B2="",C2=""),"Please complete column B and C",IF(AND(B2="",C2<>""),"Please complete column B",IF(AND(B2<>"",C2=""),"Please complete column C",IF(AND(B2="No",C2<>""),"Column B is filled as NO but column C has a number, check again",IF(AND(B2="Yes",C2=0),"Column B is filled as YES but column C is filled with ZERO, check again","Complete")))))

 

[Eric W]

Another option:

Book1
	A	B	C	D
1	Branch	Info Counter	% of Customers managed at Info Counter from the Total Tickets per Day	Message
2	Texas			Please complete columns B and C
3	Oklahoma	Yes		Please complete column C
4	Nevada		5	Please complete column B
5	Georgia	No	6	Column B is filled with No but column C is >zero, check again
6	Kansas	No	0	Complete
7	Alaska	Yes	7	Complete
8	New Jersey	Yes	0	Column B is filled with Yes but column C is zero, check again
9
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=LET(b,[@[Info Counter]],c,[@[% of Customers managed at Info Counter from the Total Tickets per Day]],d,(b<>"")*2+(c<>"")+1,x,XOR(b="NO",c>0),IF(d<4,"Please complete column"&CHOOSE(d,"s B and C"," B"," C"),IF(x,"Complete","Column B is filled with "&b&" but column C is "&IF(c=0,"",">")&"zero, check again")))

 

[Sufiyan97]

Updated the formula after comparing results with Eric's formula

=IF(AND(B2="",C2=""),"Please complete column B and C",IF(AND(B2="",C2<>""),"Please complete column B",IF(AND(B2<>"",C2=""),"Please complete column C",IF(AND(B2="No",C2>0),"Column B is filled as NO but column C has a number, check again",IF(AND(B2="Yes",C2=0),"Column B is filled as YES but column C is filled with ZERO, check again","Complete")))))

 

[kumara_faith]

Hi Sufiyan and Eric,

Both solution worked. Thank you for your patience and have a great day ahead. Thanks.

 

[Sufiyan97]

You're welcome.
Glad we could help!