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

14
votes

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?

4
excelif-statementexcel-formulacontains
just =AND(A1:D1) – Dmitry Pavliv
This should be the answer. – ttaaoossuuuu
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

4 Answers

25
votes

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"))
6
votes

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")
3
votes
=IF(COUNTIF(A1:D1,FALSE)>0,FALSE,TRUE)

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

1
votes
=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