Question

Google Sheet formula to evaluate three boolean cells


Userlevel 3
Badge +1

can't think logically this morning. i have 3 boolean cells with true/false in a spreadsheet. i'm trying to create a formula to evaluate the three, and return TRUE if all three are false. AND? OR? NOT? IFS?


12 replies

Userlevel 5
Badge +2

I think =and(A2=FALSE,B2=FALSE,C2=FALSE) should do it


Open Thread in Slack
Badge

I have some very long strings of IF in this where I had to use a different logic, but with only three variables, Mike's solution will probably do.

=ARRAYFORMULA(IF(ROW(A:A)=1;"STATUS";IF(LEN(A:A);IF((EXACT(E:E;"/")*NOT(F:F=""))>0;"ROOT";IF(E:E = F:F;"PERFECT MATCH";IF(REGEXMATCH(F:F;"Elever")*REGEXMATCH(E:E;"Medarbetare")>0;"MISMATCH D";IF(REGEXMATCH(F:F;"Medarbetare")*REGEXMATCH(E:E;"Medarbetare")>0;"MISMATCH C";IF(REGEXMATCH(F:F;"Elever")*REGEXMATCH(F:F;E:E & "")>0;"MATCH SCHOOL";IF(REGEXMATCH(F:F;"Medarbetare")*REGEXMATCH(F:F;E:E & "")>0;"MISMATCH B";"MISMATCH A"))))));)))


Open Thread in Slack
Userlevel 1
Badge +1

=IF(COUNTIF(A1:C1,"FALSE")=3,"TRUE","FALSE")


Open Thread in Slack
Userlevel 3
Badge +1

thanks went with something similar to @mike-bc’s suggestion. =and(if(G9,false,true),if(H9,false,true),if(I9,false,true))


Open Thread in Slack
Userlevel 3
Badge +1

just for context, looking to check spf, dkim, and dmarc for all the customers.

https://developers.cloudflare.com/1.1.1.1/fun-stuff/dns-in-google-sheets

regexextract to extract spf from TXT records
regexmatch to see if SPF contains and TXT records for google. and are formatted correctly.


Open Thread in Slack
Userlevel 5
Badge +2

that's really cool!


Open Thread in Slack
Badge

@brian.kim you get the results in rows, and not columns?



Open Thread in Slack
Userlevel 3
Badge +1

I have it in columns so I can filter true/false



Open Thread in Slack
Badge

@brian.kim yes, I meant, the script as it is, does the info come in rows or columns, for each host? One row for each host, with many columns, for each value.



Open Thread in Slack
Userlevel 3
Badge +1

oh just a single cell. for if there are multiple TXT records, you will have to do regexextract to find what you need.



Open Thread in Slack
Badge

Ah, so if I have a column with hosts the script will return a single column with results, and if I want I can use regextract to find? (Yes, I didn't read the code 🙂). JKust looked at the pictures and read your suggestion. 🙂



Open Thread in Slack
Userlevel 3
Badge +1

yep, basically =nslookup("TXT",domain.com)



Open Thread in Slack

Reply