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.