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.