COUNT and COUNTA functions in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to count only numbers in the range A1:A10.

Excel

=COUNT([1])

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

AA1:A10, B1:B10

BA1

CCOUNT(A1:A10)

DA1:A10

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNTA instead of COUNT when only numbers are needed.

Including multiple ranges separated by commas incorrectly.

Putting the function call inside itself.

The COUNT function counts only numeric values in the specified range. Here, A1:A10 is the correct range to count numbers.

2fill in blank

medium

Complete the formula to count all non-empty cells in the range B1:B15.

Excel

=COUNTA([1])

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

ACOUNTA(B1:B15)

BB1:B15

CB1

DB1:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT instead of COUNTA when counting text cells.

Specifying a smaller range than needed.

Putting the function call inside itself.

The COUNTA function counts all non-empty cells, including numbers, text, and other data types. The range B1:B15 covers all cells to count.

3fill in blank

hard

Fix the error in the formula to count numbers in C1:C20.

Excel

=COUNT([1])

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

AC1:C20

BC1;C20

CC1-C20

DCOUNT(C1:C20)

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolon or dash instead of colon in range.

Putting the function call inside itself.

Using a single cell instead of a range.

The correct range syntax uses a colon : between start and end cells. Semicolons or dashes are invalid here.

4fill in blank

hard

Fill both blanks to create a formula that counts non-empty cells in D1:D12 and ignores empty cells.

Excel

=COUNTA([1]) - COUNTBLANK([2])

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

AD1:D12

BD1:D10

DD1:D15

Attempts:

3 left

💡 Hint

Common Mistakes

Using different ranges for COUNTA and COUNTBLANK.

Using a smaller or larger range than needed.

Confusing COUNTBLANK with COUNT.

Both COUNTA and COUNTBLANK must use the same range D1:D12 to correctly count non-empty cells minus blanks.

5fill in blank

hard

Fill all three blanks to create a formula that counts numbers in E1:E20, counts all non-empty cells in F1:F20, and subtracts empty cells in F1:F20.

Excel

=COUNT([1]) + COUNTA([2]) - COUNTBLANK([3])

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

AE1:E20

BF1:F20

DE1:E15

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up ranges between E and F columns.

Using incorrect ranges like E1:E15 instead of E1:E20.

Confusing COUNTBLANK with COUNTA.

The formula counts numbers in E1:E20, counts all non-empty cells in F1:F20, and subtracts empty cells in F1:F20 to get accurate counts.

Practice

1. What does the COUNT function do in Excel?

easy

A. Counts only cells with numbers

B. Counts all non-empty cells

C. Counts only empty cells

D. Counts cells with text only

Solution

1. Step 1: Understand the purpose of COUNT

   The COUNT function counts only cells that contain numbers, ignoring text or empty cells.

2. Step 2: Compare with other options

   COUNTA counts all non-empty cells, so it is not the same as COUNT.

3. Final Answer:

   Counts only cells with numbers -> Option A

4. Quick Check:

   COUNT counts numbers only [OK]

Hint: COUNT counts numbers only, not text or blanks [OK]

Common Mistakes:

• Thinking COUNT counts all non-empty cells
• Confusing COUNT with COUNTA
• Assuming COUNT counts text cells

2. Which of these formulas correctly counts all non-empty cells in range A1:A5?

easy

A. =COUNTA(A1:A5)

B. =COUNT(A1:A5)

C. =COUNTBLANK(A1:A5)

D. =COUNTIF(A1:A5, "*")

Solution

1. Step 1: Identify the function that counts all non-empty cells

   COUNTA counts all cells that are not empty, including numbers, text, and errors.

2. Step 2: Check other options

   COUNT counts only numbers, COUNTBLANK counts empty cells, COUNTIF with "*" counts cells with text but not numbers.

3. Final Answer:

   =COUNTA(A1:A5) -> Option A

4. Quick Check:

   COUNTA counts all non-empty cells [OK]

Hint: Use COUNTA to count all filled cells, not just numbers [OK]

Common Mistakes:

• Using COUNT instead of COUNTA for all data
• Confusing COUNTBLANK with COUNTA
• Using COUNTIF("*") which misses numbers

3. Given the cells A1=10, A2="Hello", A3=, A4=5, A5="", what is the result of =COUNT(A1:A5)?

medium

A. 3

B. 1

C. 2

D. 4

Solution

1. Step 1: Identify which cells contain numbers

   Cells A1=10 and A4=5 are numbers. A2 is text, A3 is empty, A5 is empty string (counts as empty).

2. Step 2: COUNT counts only numbers

   So COUNT(A1:A5) counts 2 cells with numbers.

3. Final Answer:

   2 -> Option C

4. Quick Check:

   COUNT counts only numbers = 2 [OK]

Hint: COUNT counts only numeric cells, ignore text and blanks [OK]

Common Mistakes:

• Counting text cells as numbers
• Counting empty strings as non-empty
• Confusing COUNT with COUNTA

4. You want to count all non-empty cells in B1:B6, but your formula =COUNT(B1:B6) returns 3. What is the likely problem?

medium

A. The formula syntax is incorrect

B. B1:B6 contains text and blanks, COUNT counts only numbers

C. COUNT counts empty cells by mistake

D. You need to use COUNTBLANK instead

Solution

1. Step 1: Understand what COUNT does

   COUNT counts only numeric cells, ignoring text and blanks.

2. Step 2: Identify why result is 3

   If B1:B6 has text or blanks, COUNT returns only the number cells, so 3 means 3 numeric cells.

3. Final Answer:

   B1:B6 contains text and blanks, COUNT counts only numbers -> Option B

4. Quick Check:

   COUNT counts numbers only, so text cells are ignored [OK]

Hint: Use COUNTA to count all filled cells, not COUNT [OK]

Common Mistakes:

• Assuming COUNT counts text cells
• Using COUNTBLANK to count filled cells
• Thinking formula syntax is wrong

5. You have a list in C1:C8 with numbers, text, and some empty cells. Which formula counts all cells that are not empty, including numbers and text, but excludes empty cells?

hard

A. =COUNTBLANK(C1:C8)

B. =COUNT(C1:C8)

C. =COUNTIF(C1:C8, "<>")

D. =COUNTA(C1:C8)

Solution

1. Step 1: Identify the function that counts all non-empty cells

   COUNTA counts all cells that are not empty, including numbers and text.

2. Step 2: Check other options

   COUNT counts only numbers, COUNTIF with "<>" counts non-empty but may miss some cases, COUNTBLANK counts empty cells.

3. Final Answer:

   =COUNTA(C1:C8) -> Option D

4. Quick Check:

   COUNTA counts all non-empty cells [OK]

Hint: Use COUNTA to count all filled cells, numbers or text [OK]

Common Mistakes:

• Using COUNT which misses text cells
• Using COUNTBLANK which counts empty cells
• Using COUNTIF("<>") which can be tricky