I've added a duplicate of your Sheet1 (called "Erik Help"), which contains the following formula in E1:
=ArrayFormula({"E"; IF(A2:A="",, IF((LOWER(TRIM(A2:A))="stackexchange")+(B2:B+C2:C+D2:D=0),TRUE,FALSE))})
The curly brackets { }
create a literal array, meaning we can arrange the data between them below or to the right of each other.
First, the header is created, the text of which you can change within the formula as you like.
The colon means "begin the rest below."
IF(A2:A="",, )
: If a cell in A2:A
is blank, the corresponding cell in E2:E
will also be left blank/null.
IF( ,TRUE,FALSE)
: IF
the condition in the first parameter is met for the current row, then return TRUE
to the corresponding row in E2:E
.
( ) + ( )
: The condition is compound. The plus sign means OR
here; so if either condition (or both) is met, then our designated TRUE
will be returned. If neither is met, then FALSE
will be returned.
(LOWER(TRIM(A2:A))="stackexchange") + (B2:B+C2:C+D2:D=0)
: The two conditions are that a lowercase version of A2:A
without any extra spaces is exactly equal to "stackexchange"
. The second condition is that the value of Column B plus the value of Column C plus the value of Column D is equal to zero. Since FALSE
carries an equivalent value of 0 (with TRUE
carrying a value of non-zero), then the only way to arrive at 0 in adding the values of Columns B, C and D is if each is either FALSE
or 0 or null.
Notes:
In all cases within the formula, notice that TRUE
and FALSE
are not contained within quotation marks (i.e., "TRUE"
and "FALSE"
). That is because your post shows a return of Boolean values TRUE
and FALSE
, whereas "TRUE"
and "FALSE"
are seen as strings. And those are two different types that behave differently.
If it is a likely scenario that you may have something entered into Column A of a row but that there may be blanks in B:C, and that you want blanks not to be the same thing as FALSE
, you'll need one more condition and will need to use the following formula version:
=ArrayFormula({"E"; IF(A2:A="",, IF((LOWER(TRIM(A2:A))="stackexchange")+(LEN(B2:B&C2:C&D2:D)>=15),TRUE,FALSE))})
Here, the second parenthetical condition has been replaced with (LEN(B2:B&C2:C&D2:D)>=15)
. Since three entries of FALSE
would be FALSEFALSEFALSE
, this would then be the only combination of TRUE
, FALSE
and null (or minimal stray spaces) that would satisfy >=15
.