google sheets – Print True or False in calculated column using ARRAYFORMULA

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.


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.