AND function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of AND with multiple conditions

What is the result of the formula =AND(A1>5, B1<10, C1=20) if A1=6, B1=9, and C1=20?

A TRUE

B FALSE

C 0

D #VALUE!

💡 Hint

AND returns TRUE only if all conditions are TRUE.

Since A1>5 (6>5), B1<10 (9<10), and C1=20 (20=20) are all TRUE, AND returns TRUE.

❓ Function Choice

intermediate

Choosing the correct AND formula for a condition

You want to check if the value in cell A2 is between 10 and 20 inclusive. Which formula correctly uses the AND function?

A =AND(A2&gt;=10, A2&lt;20)

B =AND(A2&gt;=10, A2&lt;=20)

C =AND(A2&gt;10, A2&lt;20)

D =AND(A2&gt;10, A2&lt;=20)

💡 Hint

Inclusive means the value can be equal to the limits.

To include 10 and 20, use >= and <= operators in AND.

📊 Formula Result

advanced

Result of AND with mixed TRUE and FALSE

What is the output of =AND(TRUE, FALSE, TRUE)?

A TRUE

B #VALUE!

C 0

D FALSE

💡 Hint

AND returns TRUE only if all arguments are TRUE.

Since one argument is FALSE, AND returns FALSE.

🎯 Scenario

advanced

Using AND to validate data entry

You want to allow data entry in cell B2 only if cell A2 contains a number greater than 0 and less than 100. Which formula would you use in data validation's custom formula box?

A =AND(A2&gt;0, A2&lt;100)

B =AND(ISNUMBER(B2), B2&gt;0, B2&lt;100)

C =AND(ISNUMBER(A2), A2&gt;0, A2&lt;100)

D =AND(ISNUMBER(A2), B2&gt;0, B2&lt;100)

💡 Hint

Check the cell you want to validate and ensure it is a number within range.

The formula checks if A2 is a number and between 0 and 100, which is the condition for allowing entry.

❓ data_analysis

expert

Counting rows meeting multiple conditions with AND

You have a table with columns: Age (A), Score (B), and Status (C). You want to count how many rows have Age >= 18, Score > 70, and Status equal to "Pass". Which formula correctly counts these rows?

A =COUNTIFS(A2:A100, ">=18", B2:B100, ">70", C2:C100, "Pass")

B =SUMPRODUCT(--(A2:A100&gt;=18), --(B2:B100&gt;70), --(C2:C100="Pass"))

C =SUM(IF(AND(A2:A100&gt;=18, B2:B100&gt;70, C2:C100="Pass"), 1, 0))

D =COUNTIF(A2:A100, ">=18") + COUNTIF(B2:B100, ">70") + COUNTIF(C2:C100, "Pass")

💡 Hint

COUNTIFS counts rows matching all criteria together.

COUNTIFS applies all conditions row-wise and counts rows meeting all criteria.

Practice

1. What does the AND function do in Excel?

easy

A. Returns TRUE if any one condition inside it is true.

B. Returns TRUE if all conditions inside it are true.

C. Returns the sum of all conditions.

D. Returns FALSE if all conditions are true.

Solution

1. Step 1: Understand the purpose of AND

   The AND function checks multiple conditions and returns TRUE only if every condition is true.

2. Step 2: Compare options with AND behavior

   Returns TRUE if all conditions inside it are true. correctly states that AND returns TRUE only if all conditions are true, which matches the function's behavior.

3. Final Answer:

   Returns TRUE if all conditions inside it are true. -> Option B

4. Quick Check:

   AND = TRUE if all true [OK]

Hint: AND needs all TRUE conditions to return TRUE [OK]

Common Mistakes:

• Thinking AND returns TRUE if any condition is true
• Confusing AND with OR function
• Assuming AND sums values

2. Which of these is the correct syntax for the AND function in Excel?

easy

A. AND(condition1; condition2)

B. AND[condition1, condition2]

C. AND{condition1, condition2}

D. AND(condition1, condition2)

Solution

1. Step 1: Recall Excel function syntax

   Excel functions use parentheses () and separate arguments with commas (,).

2. Step 2: Check each option's syntax

   AND(condition1, condition2) uses parentheses and commas correctly. The other options use incorrect separators or brackets.

3. Final Answer:

   AND(condition1, condition2) -> Option D

4. Quick Check:

   Correct syntax uses commas inside parentheses [OK]

Hint: Use commas inside parentheses for function arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using square or curly brackets
• Omitting parentheses

3. Given the values in cells: A1=5, B1=10, C1=5, what is the result of =AND(A1=5, B1>5, C1<10)?

medium

A. 5

B. FALSE

C. TRUE

D. Error

Solution

1. Step 1: Evaluate each condition

   A1=5 is TRUE (5 equals 5), B1>5 is TRUE (10 is greater than 5), C1<10 is TRUE (5 is less than 10).

2. Step 2: Apply AND logic

   Since all three conditions are TRUE, AND returns TRUE.

3. Final Answer:

   TRUE -> Option C

4. Quick Check:

   All conditions true = TRUE [OK]

Hint: Check each condition; all must be true for AND to return TRUE [OK]

Common Mistakes:

• Assuming AND returns FALSE if one condition is true
• Confusing comparison operators
• Ignoring cell values

4. Identify the error in this formula: =AND(A1=5 B1>3)

medium

A. Missing comma between conditions

B. Wrong comparison operator

C. Extra parentheses

D. AND function cannot compare numbers

Solution

1. Step 1: Review formula syntax

   The AND function requires commas to separate multiple conditions inside the parentheses.

2. Step 2: Identify the missing comma

   The formula lacks a comma between A1=5 and B1>3, causing a syntax error.

3. Final Answer:

   Missing comma between conditions -> Option A

4. Quick Check:

   Separate conditions with commas in AND [OK]

Hint: Separate conditions with commas inside AND [OK]

Common Mistakes:

• Forgetting commas between conditions
• Using semicolons instead of commas
• Misplacing parentheses

5. You want to check if a student passed both Math and English. Passing score is 50. Math score is in A2, English in B2. Which formula correctly returns TRUE only if both scores are 50 or more?

hard

A. =AND(A2>=50, B2>=50)

B. =AND(A2>50, B2>50)

C. =OR(A2>=50, B2>=50)

D. =AND(A2>50 B2>50)

Solution

1. Step 1: Understand the passing condition

   Passing means scores are 50 or more, so use >=50 for both subjects.

2. Step 2: Choose correct AND formula

   =AND(A2>=50, B2>=50) uses AND with >=50 for both A2 and B2, correctly checking both conditions. =AND(A2>50, B2>50) uses >50 which excludes 50 exactly. =OR(A2>=50, B2>=50) uses OR, which returns TRUE if only one subject passes. =AND(A2>50 B2>50) misses a comma, causing syntax error.

3. Final Answer:

   =AND(A2>=50, B2>=50) -> Option A

4. Quick Check:

   Use AND with >= for both scores [OK]

Hint: Use >= and commas inside AND for both conditions [OK]

Common Mistakes:

• Using > instead of >= excludes exact passing score
• Using OR instead of AND
• Missing commas between conditions