COUNTIF and COUNTIFS in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

COUNTIF with Text Criteria

You have a list of fruits in cells A1:A10. Some cells contain the word "Apple". What formula counts how many cells exactly contain "Apple"?

A =COUNTIF(A1:A10, "Apple")

B =COUNTIF(A1:A10, "*Apple*")

C =COUNTIF(A1:A10, "=Apple")

D =COUNTIF(A1:A10, "Apple*")

💡 Hint

Use COUNTIF with the exact text as the criteria.

The formula =COUNTIF(A1:A10, "Apple") counts cells that exactly match "Apple". Other options use wildcards or extra symbols that change the match.

📊 Formula Result

intermediate

COUNTIFS with Multiple Conditions

You have a table with sales data: Column A has product names, Column B has sales amounts. Which formula counts rows where product is "Banana" and sales are greater than 100?

A =COUNTIF(A1:A20, "Banana") + COUNTIF(B1:B20, ">100")

B =COUNTIFS(A1:A20, "Banana", B1:B20, "<100")

C =COUNTIFS(A1:A20, "Banana", B1:B20, "=100")

D =COUNTIFS(A1:A20, "Banana", B1:B20, ">100")

💡 Hint

Use COUNTIFS to apply both conditions together.

The formula =COUNTIFS(A1:A20, "Banana", B1:B20, ">100") counts rows where both conditions are true. Option D adds counts separately, which is incorrect.

❓ Function Choice

advanced

Choosing the Right Function for Partial Text Match

You want to count how many cells in range C1:C15 contain the word "berry" anywhere inside the text. Which formula correctly does this?

A =COUNTIFS(C1:C15, "berry")

B =COUNTIF(C1:C15, "*berry*")

C =COUNTIF(C1:C15, "berry*")

D =COUNTIFS(C1:C15, "*berry*")

💡 Hint

COUNTIF supports wildcards for partial matches.

=COUNTIF(C1:C15, "*berry*") counts cells containing "berry" anywhere. COUNTIFS requires pairs of ranges and criteria, so option B is invalid syntax.

📊 Formula Result

advanced

COUNTIFS with Date and Number Conditions

You have dates in D1:D30 and sales in E1:E30. Which formula counts rows where the date is after 2023-01-01 and sales are at least 500?

A =COUNTIFS(D1:D30, ">2023-01-01", E1:E30, ">=500")

B =COUNTIFS(D1:D30, ">=2023-01-01", E1:E30, ">500")

C =COUNTIFS(D1:D30, ">=2023-01-01", E1:E30, ">=500")

D =COUNTIFS(D1:D30, ">2023-01-01", E1:E30, ">500")

💡 Hint

Use >= for dates on or after and >= for sales at least 500.

Option C uses correct operators: date >= 2023-01-01 and sales >= 500. Other options use > which excludes the date or sales equal to the boundary.

🎯 Scenario

expert

Complex COUNTIFS Scenario with Mixed Criteria

You have a sheet with columns: Product (A), Region (B), Sales (C), and Date (D). You want to count how many sales of "Orange" happened in "West" region, with sales between 200 and 500 inclusive, and dates in 2023 only. Which formula gives the correct count?

A =COUNTIFS(A:A, "Orange", B:B, "West", C:C, ">=200", C:C, "<=500", D:D, ">=2023-01-01", D:D, "<=2023-12-31")

B =COUNTIFS(A:A, "Orange", B:B, "West", C:C, ">200", C:C, "<500", D:D, ">=2023-01-01", D:D, "<=2023-12-31")

C =COUNTIFS(A:A, "Orange", B:B, "West", C:C, ">=200", C:C, "<=500", D:D, ">2023-01-01", D:D, "<2023-12-31")

D =COUNTIFS(A:A, "Orange", B:B, "West", C:C, ">=200", C:C, "<=500", D:D, "2023")

💡 Hint

Use inclusive operators for sales and date range covering all 2023 dates.

Option A correctly uses >= and <= for sales and dates to include boundaries. Option A excludes boundary values. Option A excludes first and last day of 2023. Option A uses an invalid date criteria format.