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.

Practice

1. What does the COUNTIF function do in Google Sheets?

easy

A. Adds values in cells that meet a condition.

B. Counts the number of cells that meet a single condition.

C. Counts the total number of cells in a range.

D. Finds the average of cells that meet multiple conditions.

Solution

1. Step 1: Understand the purpose of COUNTIF

   COUNTIF is designed to count cells that satisfy one specific condition.

2. Step 2: Compare with other functions

   Unlike SUMIF or AVERAGEIFS, COUNTIF only counts cells, not sums or averages.

3. Final Answer:

   Counts the number of cells that meet a single condition. -> Option B

4. Quick Check:

   COUNTIF counts cells with one condition [OK]

Hint: COUNTIF counts cells matching one rule only [OK]

Common Mistakes:

• Confusing COUNTIF with SUMIF
• Thinking COUNTIF handles multiple conditions
• Assuming COUNTIF calculates averages

2. Which of the following is the correct syntax for the COUNTIFS function?

easy

A. COUNTIFS(range, criterion)

B. COUNTIFS(criterion1, range1, criterion2, range2)

C. COUNTIFS(range1, criterion1, range2, criterion2)

D. COUNTIFS(range1; criterion1; range2; criterion2)

Solution

1. Step 1: Recall COUNTIFS syntax

   COUNTIFS requires pairs of ranges and criteria: first range1 with criterion1, then range2 with criterion2, etc.

2. Step 2: Check option formats

   COUNTIFS(range1, criterion1, range2, criterion2) correctly shows pairs in order with commas. COUNTIFS(range1; criterion1; range2; criterion2) uses semicolons which is incorrect in Google Sheets.

3. Final Answer:

   COUNTIFS(range1, criterion1, range2, criterion2) -> Option C

4. Quick Check:

   COUNTIFS needs range-criterion pairs separated by commas [OK]

Hint: COUNTIFS needs pairs: range then criterion, separated by commas [OK]

Common Mistakes:

• Swapping order of range and criterion
• Using semicolons instead of commas
• Providing only one range and criterion

3. Given the data in column A: {10, 20, 30, 40, 50}, what is the result of =COUNTIFS(A1:A5, ">=20", A1:A5, "<=40")?

medium

A. 3

B. 5

C. 4

D. 2

Solution

1. Step 1: Identify values meeting first condition

   Values >=20 are 20, 30, 40, 50 (4 values).

2. Step 2: Identify values meeting second condition

   Values <=40 are 10, 20, 30, 40 (4 values).

3. Step 3: Find values meeting both conditions

   Common values are 20, 30, 40 (3 values).

4. Final Answer:

   3 -> Option A

5. Quick Check:

   COUNTIFS counts cells meeting all conditions [OK]

Hint: COUNTIFS counts cells meeting all conditions simultaneously [OK]

Common Mistakes:

• Counting values that meet only one condition
• Misreading comparison operators
• Including values outside both conditions

4. You wrote =COUNTIFS(A1:A5, ">10", B1:B5, "<20") but it returns 0. The data in A1:A5 is {5, 15, 25, 35, 45} and in B1:B5 is {10, 20, 20, 25, 30}. What is the error?

medium

A. The formula syntax is incorrect; COUNTIFS needs only one range.

B. The ranges A1:A5 and B1:B5 must be the same size but they are not.

C. COUNTIFS cannot handle numeric criteria with comparison operators.

D. The criteria should be ">=10" and "<=20" to include boundary values.

Solution

1. Step 1: Analyze criteria and data

   Criteria ">10" qualifies 15, 25, 35, 45 in A (rows 2-5). Criteria "<20" qualifies only 10 in B (row 1).

2. Step 2: Check which rows meet both conditions

   Rows where A>10 and B<20: none. Row 1 has B=10<20 but A=5 not >10. Rows 2-3 have A>10 but B=20 not <20. Rows 4-5 have B>20.

3. Step 3: Verify why result is 0

   Formula returns 0 because strict inequalities exclude boundary values like B=20. Changing to ">=10" and "<=20" includes boundaries.

4. Final Answer:

   The criteria should be ">=10" and "<=20" to include boundary values. -> Option D

5. Quick Check:

   Use inclusive operators to count boundary values [OK]

Hint: Use >= and <= to include boundary numbers in COUNTIFS [OK]

Common Mistakes:

• Using > or < excludes boundary values unintentionally
• Assuming COUNTIFS ignores criteria order
• Thinking COUNTIFS needs only one range

5. You have a sales table with columns: Region (A2:A10), Sales (B2:B10). You want to count how many sales are greater than 1000 in the "East" region. Which formula correctly does this?

hard

A. COUNTIFS(A2:A10, "East", B2:B10, ">1000")

B. COUNTIF(A2:A10, "East") + COUNTIF(B2:B10, ">1000")

C. COUNTIFS(A2:A10, ">1000", B2:B10, "East")

D. COUNTIF(B2:B10, ">1000")

Solution

1. Step 1: Understand the conditions

   You want to count rows where Region is "East" AND Sales > 1000.

2. Step 2: Choose correct function and order

   COUNTIFS counts cells meeting multiple conditions. The first range is Region with criterion "East", second range is Sales with criterion ">1000".

3. Step 3: Evaluate other options

   COUNTIF(A2:A10, "East") + COUNTIF(B2:B10, ">1000") adds counts separately, which counts rows twice incorrectly. COUNTIFS(A2:A10, ">1000", B2:B10, "East") swaps ranges and criteria incorrectly. COUNTIF(B2:B10, ">1000") counts only sales > 1000 ignoring region.

4. Final Answer:

   COUNTIFS(A2:A10, "East", B2:B10, ">1000") -> Option A

5. Quick Check:

   COUNTIFS counts rows matching all conditions [OK]

Hint: Use COUNTIFS with range-criterion pairs for multiple conditions [OK]

Common Mistakes:

• Adding separate COUNTIF results instead of using COUNTIFS
• Swapping criteria and ranges
• Ignoring one condition in formula