AND and OR functions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of combined AND and OR functions

What is the output of the formula =AND(OR(TRUE, FALSE), OR(FALSE, FALSE)) in Google Sheets?

Google Sheets

=AND(OR(TRUE, FALSE), OR(FALSE, FALSE))

A Error

B TRUE

C FALSE

D 0

💡 Hint

Remember OR returns TRUE if any argument is TRUE; AND returns TRUE only if all arguments are TRUE.

OR(TRUE, FALSE) returns TRUE; OR(FALSE, FALSE) returns FALSE; AND(TRUE, FALSE) returns FALSE.

❓ Function Choice

intermediate

Choose the formula that returns TRUE only if all conditions are TRUE

Which formula returns TRUE only if all three cells A1, B1, and C1 contain values greater than 10?

A =AND(A1>10, OR(B1>10, C1>10))

B =AND(A1>10, B1>10, C1>10)

C =OR(A1>10, B1>10, C1>10)

D =OR(AND(A1>10, B1>10), C1>10)

💡 Hint

AND requires all conditions to be TRUE; OR requires only one.

Only option B uses AND on all three conditions, so it returns TRUE only if all are greater than 10.

🎯 Scenario

advanced

Detect if a student passes based on multiple criteria

You want to mark a student as 'Pass' if their score in Math (A2) is at least 50, and either their score in English (B2) or Science (C2) is at least 60. Which formula correctly returns TRUE for passing students?

A =AND(A2>=50, OR(B2>=60, C2>=60))

B =OR(AND(A2>=50, B2>=60), C2>=60)

C =AND(OR(A2>=50, B2>=60), C2>=60)

D =OR(A2>=50, AND(B2>=60, C2>=60))

💡 Hint

Passing requires Math score at least 50 AND (English OR Science score at least 60).

Option A matches the condition exactly: Math >=50 AND (English >=60 OR Science >=60).

📊 Formula Result

advanced

Result of nested AND and OR with FALSE values

What is the result of the formula =OR(AND(TRUE, FALSE), AND(FALSE, FALSE), TRUE) in Google Sheets?

Google Sheets

=OR(AND(TRUE, FALSE), AND(FALSE, FALSE), TRUE)

A TRUE

B FALSE

C Error

D 0

💡 Hint

OR returns TRUE if any argument is TRUE; AND returns TRUE only if all arguments are TRUE.

AND(TRUE, FALSE) is FALSE; AND(FALSE, FALSE) is FALSE; OR(FALSE, FALSE, TRUE) is TRUE.

❓ data_analysis

expert

Count rows where multiple conditions are met using AND and OR

You have a table with columns A (Age), B (City), and C (Membership). You want to count how many rows have Age over 30 AND (City is 'New York' OR Membership is 'Gold'). Which formula correctly counts these rows?

A =COUNTIFS(A:A, ">30", B:B, "New York") + COUNTIFS(A:A, ">30", C:C, "Gold")

B =COUNTIFS(A:A, ">30", B:B, "New York", C:C, "Gold")

C ))0>)"dloG"=C:C(+)"kroY weN"=B:B((*)03>A:A((TCUDORPMUS=

D =SUMPRODUCT((A:A>30)*((B:B="New York")+(C:C="Gold")>0))

💡 Hint

Use SUMPRODUCT with logical tests and ensure OR condition is handled correctly.

Option D correctly sums rows where Age>30 and either City='New York' or Membership='Gold'. The '>0' inside the parentheses ensures the OR condition is evaluated properly.