what is wrong with my AND formula?

[pcorpz]

=IF(AND(B3="FALSE",C3="FALSE",D3="FALSE",E3="FALSE",F3="FALSE",G3="FALSE",H3="FALSE",I3="FALSE"),"Valid","Bounced")

My apologies if my formula could have been written a better way.. but basically, I want excel to return Valid if all columns are "FALSE".. and Bounced if one or two more columns have a value other than FALSE.

Some other values would be blank, or unknown_user.. etc.

But the formula only returned "Bounced" so I know I'm missing something...

 

[Rick Rothstein]

=IF(AND(B3="FALSE",C3="FALSE",D3="FALSE",E3="FALSE",F3="FALSE",G3="FALSE",H3="FALSE",I3="FALSE"),"Valid","Bounced")

I am guessing that the values in B3:I3 are real Booleans (TRUE and/or FALSE, no quote marks), so you would need to remove the quote marks from around the FALSE keywords (real Booleans are not the same as quoted Text strings that look like Booleans). With that said, I think this shorter formula will do what your formula is attempting to do...

=IF(COUNTIF(B3:I3,TRUE),"Bounced","Valid")

 

[pcorpz]

=IF(AND(B3="FALSE",C3="FALSE",D3="FALSE",E3="FALSE",F3="FALSE",G3="FALSE",H3="FALSE",I3="FALSE"),"Valid","Bounced")

My apologies if my formula could have been written a better way.. but basically, I want excel to return Valid if all columns are "FALSE".. and Bounced if one or two more columns have a value other than FALSE.

Some other values would be blank, or unknown_user.. etc.

But the formula only returned "Bounced" so I know I'm missing something...

I was doing some tests, and replaced the text "False" to something else, as I thought "False" is also used in excel formula, and that might create a conflict....so I replaced False with No... and my formula worked!!! Weird.!

 

[Rick Rothstein]

I was doing some tests, and replaced the text "False" to something else, as I thought "False" is also used in excel formula, and that might create a conflict....so I replaced False with No... and my formula worked!!! Weird.!

See what I wrote in Message #2 about Booleans and quoted Text strings. When you replaced the Boolean values (TRUE and FALSE without quote marks in the cells) with "No" (a quoted Text string), then your test of quoted Text strings with quoted Text strings worked.

 

[pcorpz]

I am guessing that the values in B3:I3 are real Booleans (TRUE and/or FALSE, no quote marks), so you would need to remove the quote marks from around the FALSE keywords (real Booleans are not the same as quoted Text strings that look like Booleans). With that said, I think this shorter formula will do what your formula is attempting to do...

=IF(COUNTIF(B3:I3,TRUE),"Bounced","Valid")

Thanks Rick! the values are not real booleans.. since there's more values than true and false? Didn't think of using countif!

 

[pcorpz]

See what I wrote in Message #2 about Booleans and quoted Text strings. When you replaced the Boolean values (TRUE and FALSE without quote marks in the cells) with "No" (a quoted Text string), then your test of quoted Text strings with quoted Text strings worked.

Learned something new. =D