COUNTIF and COUNTIFS in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to count cells in range A1:A10 that equal 5.

Excel

=COUNTIF(A1:A10, [1])

Drag options to blanks, or click blank then click option'

A"5"

B5

C"=5"

D>=5

Attempts:

3 left

💡 Hint

Common Mistakes

Using 5 without quotes causes COUNTIF to not work as expected.

Using >=5 counts cells greater or equal, not just equal to 5.

The COUNTIF function needs the criteria as a text string. To count cells equal to 5, use "5" in quotes.

2fill in blank

medium

Complete the formula to count cells in B1:B20 that are greater than 10.

Excel

=COUNTIF(B1:B20, [1])

Drag options to blanks, or click blank then click option'

A">10"

B10

C>=10

D"10"

Attempts:

3 left

💡 Hint

Common Mistakes

Using 10 without operator counts cells equal to 10 only.

Using >=10 counts cells equal or greater, not strictly greater.

The criteria must be a text string with the comparison operator and number inside quotes, like ">10".

3fill in blank

hard

Fix the error in this COUNTIFS formula to count cells in A1:A10 equal to "Yes" and B1:B10 greater than 5.

Excel

=COUNTIFS(A1:A10, [1], B1:B10, [2])

Drag options to blanks, or click blank then click option'

A"Yes"

BYes

C">5"

D>5

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting text criteria causes errors.

Not quoting comparison operators causes errors.

Text criteria like "Yes" must be in quotes. Numeric criteria with comparison operators like ">5" must also be in quotes.

4fill in blank

hard

Fill both blanks to count cells in C1:C15 that are not empty and less than 100.

Excel

=COUNTIFS(C1:C15, [1], C1:C15, [2])

Drag options to blanks, or click blank then click option'

A"<>"

B"<100"

C"<=100"

D"="

Attempts:

3 left

💡 Hint

Common Mistakes

Using "=" checks for empty cells, not non-empty.

Using "<=100" includes 100, not strictly less.

"<>" means not empty. "<100" counts cells less than 100. Both criteria together count cells not empty and less than 100.

5fill in blank

hard

Fill all three blanks to count cells in D1:D20 where text contains "apple" and number in E1:E20 is greater than 50.

Excel

=COUNTIFS(D1:D20, [1], E1:E20, [2], E1:E20, [3])

Drag options to blanks, or click blank then click option'

A"*apple*"

B">50"

C"<>"

D"apple"

Attempts:

3 left

💡 Hint

Common Mistakes

Not using wildcards misses partial matches.

Not checking for non-empty cells may count blanks.

"*apple*" counts cells containing "apple" anywhere. ">50" counts numbers greater than 50. "<>" ensures the number cells are not empty.

Practice

1. What does the Excel function COUNTIF(range, criteria) do?

easy

A. Counts the number of cells in a range that meet a single condition

B. Adds all numbers in a range that meet a condition

C. Counts cells with numbers only, ignoring text

D. Finds the maximum value in a range

Solution

1. Step 1: Understand the function purpose

   The COUNTIF function is designed to count cells that match one specific condition.

2. Step 2: Compare with other options

   B describes adding numbers meeting a condition (like SUMIF), C describes counting numbers only (like COUNT), and D describes finding the maximum (like MAX), none of which is COUNTIF.

3. Final Answer:

   Counts the number of cells in a range that meet a single condition -> Option A

4. Quick Check:

   COUNTIF counts matching cells [OK]

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

Common Mistakes:

• Confusing COUNTIF with SUMIF
• Thinking COUNTIF counts multiple conditions
• Assuming COUNTIF only counts numbers

2. Which of the following is the correct syntax for counting cells in range A1:A10 that equal 5 using COUNTIF?

easy

A. =COUNTIF(A1:A10, 5)

B. =COUNTIF(5, A1:A10)

C. =COUNTIF(A1:A10, ">=5")

D. =COUNTIF(A1:A10; 5)

Solution

1. Step 1: Recall COUNTIF syntax

   The correct syntax is COUNTIF(range, criteria). The criteria can be a number without quotes.

2. Step 2: Evaluate each option

   A uses ">=5" which counts cells greater than or equal to 5, not exactly 5; B reverses arguments, which is wrong; C uses 5 directly, which is correct and simpler; D uses semicolon which is locale-dependent and may cause error in some Excel versions.

3. Final Answer:

   =COUNTIF(A1:A10, 5) -> Option A

4. Quick Check:

   COUNTIF(range, criteria) with number criteria [OK]

Hint: Use =COUNTIF(range, number) without quotes for numbers [OK]

Common Mistakes:

• Swapping range and criteria arguments
• Using semicolon instead of comma in some locales
• Using '>=5' instead of '5' for exact match

3. Given the data in cells A1:A5: {3, 5, 5, 7, 5}, what is the result of =COUNTIFS(A1:A5, ">=5", A1:A5, "<7")?

medium

A. 1

B. 2

C. 3

D. 4

Solution

1. Step 1: Understand the COUNTIFS conditions

   COUNTIFS counts cells where values are >=5 AND <7.

2. Step 2: Check each value in A1:A5

   Values: 3 (no), 5 (yes), 5 (yes), 7 (no, equals 7 but <7 fails), 5 (yes). So three 5s qualify, but 7 does not.

3. Step 3: Confirm the count

   Three values are 5, but condition is >=5 and <7, so 5 counts. So count is 3.

4. Final Answer:

   3 -> Option C

5. Quick Check:

   Values between 5 and less than 7 = 3 [OK]

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

Common Mistakes:

• Counting values equal to 7 incorrectly
• Confusing COUNTIF with COUNTIFS
• Misreading the logical AND in COUNTIFS

4. You want to count cells in B1:B10 that are greater than 10 and less than 20. Which formula has an error?

medium

A. =COUNTIFS(B1:B10, ">10", B1:B10, "<20")

B. =COUNTIF(B1:B10, ">10") + COUNTIF(B1:B10, "<20")

C. =COUNTIFS(B1:B10, "<20", B1:B10, ">10")

D. =COUNTIFS(B1:B10, ">10",B1:B10,"<20")

Solution

1. Step 1: Understand the goal

   You want to count cells greater than 10 AND less than 20, meaning both conditions must be true simultaneously.

2. Step 2: Analyze each formula

   A adds two COUNTIF results, which counts cells greater than 10 plus cells less than 20 separately, causing double counting. The other options use COUNTIFS correctly with both conditions combined.

3. Final Answer:

   =COUNTIF(B1:B10, ">10") + COUNTIF(B1:B10, "<20") -> Option B

4. Quick Check:

   Adding counts separately causes double counting [OK]

Hint: Use COUNTIFS for multiple conditions, not sum of COUNTIFs [OK]

Common Mistakes:

• Adding COUNTIF results instead of using COUNTIFS
• Misunderstanding AND vs OR logic
• Using wrong operators in criteria

5. You have a sales table with columns: Region (A2:A10), Sales (B2:B10). Which formula counts sales greater than 1000 in the "East" region?

hard

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

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

C. =COUNTIF(A2:A10, "East" & B2:B10, ">1000")

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

Solution

1. Step 1: Identify conditions and ranges

   You want to count rows where Region is "East" AND Sales > 1000. Region is in A2:A10, Sales in B2:B10.

2. Step 2: Match conditions to correct ranges

   =COUNTIFS(A2:A10, "East", B2:B10, ">1000") correctly uses COUNTIFS with Region range and criteria "East", and Sales range with ">1000". =COUNTIF(A2:A10, "East") + COUNTIF(B2:B10, ">1000") adds separate COUNTIFs, which is incorrect. =COUNTIF(A2:A10, "East" & B2:B10, ">1000") concatenates ranges incorrectly. =COUNTIFS(A2:A10, ">1000", B2:B10, "East") swaps criteria and ranges incorrectly.

3. Final Answer:

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

4. Quick Check:

   COUNTIFS matches multiple conditions on correct ranges [OK]

Hint: Use COUNTIFS with each condition and its range paired [OK]

Common Mistakes:

• Adding separate COUNTIF results instead of COUNTIFS
• Mixing up criteria and ranges
• Concatenating ranges incorrectly