Excel formula to search if all cells in a range read "True", if not, then show "False"

Question

Using an excel formula to search if all cells in a range read "True", if not, then show "False"

For example:

A      B     C     D
True  True  True   True
True  True  FALSE  True

I want a formula to read this range and show that in row 2, the was a "False" and since there are no falses in row 1 I want it to show "true."

Can anyone help me with this?

That didn't work. I want it to look at columns A-D as the range and say "False" if any falses exist. I am using this as a check and have many columns so it would be nice instead of checking each column for falses that I could look in one column and identify the rows that did show a "false." — user3384215, Mar 05 '14 at 15:43

score 34 · Answer 1 · answered Jan 09 '15 at 09:02

34

You can just AND the results together if they are stored as TRUE / FALSE values:

=AND(A1:D2)

Or if stored as text, use an array formula - enter the below and press Ctrl+Shift+Enter instead of Enter.

=AND(EXACT(A1:D2,"TRUE"))

answered Jan 09 '15 at 09:02

Simon D

1

My data is certainly 'native' TRUE/FALSE boolean values; so the straight AND(..) worked for me! – user2006616 Jun 24 '15 at 22:50

score 7 · Accepted Answer · answered Mar 05 '14 at 16:49

7

As it appears you have the values as text, and not the numeric True/False, then you can use either COUNTIF or SUMPRODUCT

=IF(SUMPRODUCT(--(A2:D2="False")),"False","True")
=IF(COUNTIF(A3:D3,"False*"),"False","True")

answered Mar 05 '14 at 16:49

SeanC

The COUNTIF formula worked! Thank you so much! I was writing the exact same formula but I didn't include the * after false. If you happen to read this, would you mind sharing why that symbol was needed? Thanks again!! – user3384215 Mar 05 '14 at 17:39
@user3384215, without the *, Excel translates `"False"` to the logical value, rather than keeping it as text. The `*` forces it into a string, which will then allow it to match the text in the cells. Note that the `*` will also mean it would match Falses, FalseStuff, and anything else that begins with those 5 letters – SeanC Mar 05 '14 at 17:45

score 3 · Answer 3 · answered Mar 05 '14 at 15:45

3

=IF(COUNTIF(A1:D1,FALSE)>0,FALSE,TRUE)

(or you can specify any other range to look in)

answered Mar 05 '14 at 15:45

ttaaoossuuuu

I tried this but it did not work. I looked on one of my rows that I knew contained a false as a check and the formula you suggested return a true. – user3384215 Mar 05 '14 at 15:49
I think it may be because the cells contain text. That why I have been confused on getting this to work. – user3384215 Mar 05 '14 at 15:51
You can simplify this by just saying `=COUNTIF(A1:D1, FALSE)=0` – Simon D Jan 09 '15 at 09:02

score 1 · Answer 4 · answered Nov 01 '19 at 21:15

1

=COUNTIFS(1:1,FALSE)=0

This will return TRUE or FALSE (Looks for FALSE, if count isn't 0 (all True) it will be false

answered Nov 01 '19 at 21:15

Geoff Hillocks

4 Answers4