COUNTIF and COUNTIFS in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

COUNTIF with Text Criteria

Given a list of fruits in cells A1:A6: {"Apple", "Banana", "Apple", "Orange", "Banana", "Apple"}, what is the result of the formula =COUNTIF(A1:A6, "Apple")?

A 3

B 2

C 1

D 6

💡 Hint

COUNTIF counts how many cells match the given condition exactly.

The formula counts how many times "Apple" appears in the range A1:A6. Since "Apple" appears 3 times, the result is 3.

📊 Formula Result

intermediate

COUNTIFS with Multiple Conditions

In a table with columns A (Names) and B (Scores), cells A1:A5 contain {"John", "Anna", "John", "Mike", "Anna"} and B1:B5 contain {85, 90, 75, 90, 85}. What is the result of =COUNTIFS(A1:A5, "Anna", B1:B5, ">85")?

A 2

B 1

C 0

D 3

💡 Hint

COUNTIFS counts rows where all conditions are true at the same time.

Anna appears twice with scores 90 and 85. Only the score 90 is greater than 85, so the count is 1.

❓ Function Choice

advanced

Choosing the Right Function for Counting

You want to count how many cells in range C1:C10 contain numbers greater than 50 and text "Passed" in range D1:D10. Which formula correctly counts rows meeting both conditions?

A =COUNTIFS(C1:C10, ">50", D1:D10, "Passed")

B =COUNTIF(C1:C10, ">50") + COUNTIF(D1:D10, "Passed")

C =COUNTIF(C1:C10, ">50") * COUNTIF(D1:D10, "Passed")

D =COUNTIF(C1:C10:D10, ">50", "Passed")

💡 Hint

Use COUNTIFS to count rows where multiple conditions are true together.

Option A uses COUNTIFS with two conditions, counting rows where C1:C10 > 50 and D1:D10 = "Passed" simultaneously. Other options either add counts separately or use invalid syntax.

🎯 Scenario

advanced

Counting Dates Within a Range

You have dates in cells E1:E10. You want to count how many dates fall between January 1, 2024, and March 31, 2024, inclusive. Which formula gives the correct count?

A =COUNTIFS(E1:E10, ">=1/1/2024", E1:E10, "<=3/31/2024")

B )")13,3,4202(ETAD=<" ,01E:1E ,")1,1,4202(ETAD=>" ,01E:1E(SFITNUOC=

C =COUNTIFS(E1:E10, ">=DATE(2024,1,1)", E1:E10, "<=DATE(2024,3,31)")

D =COUNTIF(E1:E10, ">=1/1/2024") - COUNTIF(E1:E10, ">3/31/2024")

💡 Hint

Use DATE function inside quotes for correct date comparison in formulas.

Option C correctly uses COUNTIFS with DATE function inside quotes to compare dates properly. Option C uses text dates which Excel may misinterpret. Option C subtracts counts incorrectly. Option C has quotes around DATE function making it a text string, so it won't work.

❓ data_analysis

expert

Analyzing COUNTIFS with Mixed Criteria

You have a sales table with columns: Product (A), Region (B), and Sales (C). You want to count how many sales records have Product "Widget", Region not equal to "East", and Sales greater than or equal to 100. Which formula returns the correct count?

A =COUNTIFS(A:A, "Widget", B:B, "<>East", C:C, ">100")

B =COUNTIFS(A:A, "Widget", B:B, "East", C:C, ">=100")

C =COUNTIFS(A:A, "Widget", B:B, "<>East", C:C, ">=1000")

D =COUNTIFS(A:A, "Widget", B:B, "<>East", C:C, ">=100")

💡 Hint

Use "<>" to specify not equal condition in COUNTIFS.

Option D counts rows where Product is "Widget", Region is not "East", and Sales are at least 100. Option D counts only Region "East". Option D excludes sales equal to 100. Option D requires sales >= 1000, which is stricter than needed.