0

I need to output either TRUE or FALSE in column E, based on the following criteria:

If A2 = "stackexchange" then (always, regardless of other data) print TRUE in E2 (otherwise print FALSE)

If B2=FALSE AND C2=FALSE AND D2=False then print TRUE in E2 (otherwise print FALSE)

Example data and expected result

A B C D E
stackexchange TRUE FALSE FALSE TRUE
reddit FALSE FALSE FALSE TRUE
reddit TRUE FALSE FALSE FALSE

The other requirement is that it needs to use ARRAYFORMULA as this data comes in automatically so the formula needs to be automatically copied to the rows beneath as they come in.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
0

Answering my own question because I think I figured this out:

=ArrayFormula(IF(OR(A2="stackexchange",(AND(B2=FALSE,C2=FALSE,D2=FALSE))),TRUE,FALSE))
Improve this answer
2
  • 1
    Your post tags include "google-sheets-arrayformula"; but the above is a drag-down single formula. If you are, in fact, interested in an array version, please post a link to a sheet containing the sample data shown in your post. Otherwise, if you find and are satisfied with your own answer on a post, it helps the community at large to mark it as "Best Answer," just as you would with anyone else's post. It lets contributors know at a glance from the main listings page which items have already been resolved; and it helps future site visitors most quickly find relevant solutions.
    – Erik Tyler
    Jul 8, 2021 at 16:23
  • Erik, thanks, from reading the docs my understanding was that arrayformula would auto populate new rows, but you are correct in that what I have does not. Here is a link to the sample data: docs.google.com/spreadsheets/d/… I'll mark my answer accepted for now unless/until a better one surfaces.
    – jaybong
    Jul 13, 2021 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.