COUNT and COUNTA functions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Counting Numbers vs Non-Empty Cells

Given the range A1:A6 contains the values: 5, "", "Text", 10, TRUE, and 0, what is the result of =COUNT(A1:A6)?

A 4

B 5

C 3

D 6

💡 Hint

Remember, COUNT counts only numeric values.

COUNT counts only numbers. The cells with 5, 10, and 0 are numbers. The empty cell, text, and TRUE are not counted.

📊 Formula Result

intermediate

Counting All Non-Empty Cells

Using the same range A1:A6 with values: 5, "", "Text", 10, TRUE, and 0, what is the result of =COUNTA(A1:A6)?

A 6

B 4

C 3

D 5

💡 Hint

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

COUNTA counts all non-empty cells. The empty cell is ignored. The cells with 5, "Text", 10, TRUE, and 0 are counted, totaling 5.

❓ Function Choice

advanced

Choosing the Right Function for Counting Numbers and Text

You want to count how many cells in range B1:B10 contain text but ignore empty cells and logical values. Which function should you use?

A COUNTIF(B1:B10, "*")

B COUNTA(B1:B10)

C COUNT(B1:B10)

D COUNTIF(B1:B10, "<>TRUE")

💡 Hint

Think about how to count cells with any text but exclude logical TRUE/FALSE.

COUNTIF(B1:B10, "*") counts all cells containing text but excludes empty cells, numbers, and logical values. COUNT counts only numbers. COUNTA counts numbers, text, and logical values. COUNTIF(B1:B10, "<>TRUE") is incorrect because it excludes only TRUE, not FALSE or blanks properly.

🎯 Scenario

advanced

Counting Non-Empty Cells Excluding Errors

You have a range C1:C8 with some cells containing numbers, text, empty cells, and error values like #DIV/0!. You want to count all non-empty cells but exclude error cells. Which formula will give the correct count?

A =SUMPRODUCT(--NOT(ISERROR(C1:C8)), --(C1:C8<>""))

B =COUNTA(C1:C8)

C =COUNT(C1:C8)

D =COUNTIF(C1:C8, "<>")

💡 Hint

Think about how to exclude error cells while counting non-empty cells.

COUNTA counts all non-empty cells including errors. COUNT counts only numbers, ignoring text and errors. COUNTIF(C1:C8, "<>") counts non-empty cells but includes errors. SUMPRODUCT(--NOT(ISERROR(C1:C8)), --(C1:C8<>"")) counts cells that are not errors and not empty, giving the correct count.

❓ data_analysis

expert

Analyzing COUNT and COUNTA with Mixed Data Types

Consider the range D1:D7 with the following values: 12, "Hello", TRUE, "", 0, #N/A, FALSE. What are the results of =COUNT(D1:D7) and =COUNTA(D1:D7) respectively?

A COUNT = 2, COUNTA = 7

B COUNT = 2, COUNTA = 6

C COUNT = 3, COUNTA = 7

D COUNT = 3, COUNTA = 6

💡 Hint

Recall what COUNT and COUNTA count, and how errors and logical values are treated.

COUNT counts only numbers: 12 and 0, so 2. COUNTA counts all non-empty cells including text, logical values, and errors: 12, "Hello", TRUE, 0, #N/A, FALSE, totaling 6. The empty string "" is considered empty.

Practice

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

easy

A. Counts all cells, including empty ones.

B. Counts only empty cells.

C. Counts only the cells that contain numbers.

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

   Options A, C, and D describe counting all cells, empty cells, or text cells, which COUNT does not do.

3. Final Answer:

   Counts only the cells that contain numbers. -> Option C

4. Quick Check:

   COUNT counts numbers only [OK]

Hint: Remember 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 is the correct syntax to count all non-empty cells in the range A1:A10?

easy

A. =COUNTA(A1:A10)

B. =COUNT(A1:A10)

C. =COUNTALL(A1:A10)

D. =COUNTNONEMPTY(A1:A10)

Solution

1. Step 1: Identify the function for counting non-empty cells

   The COUNTA function counts all non-empty cells, including numbers, text, and other data.

2. Step 2: Check syntax correctness

   Options C and D are not valid Google Sheets functions. =COUNT(A1:A10) counts only numbers, not all non-empty cells.

3. Final Answer:

   =COUNTA(A1:A10) -> Option A

4. Quick Check:

   COUNTA counts all non-empty cells [OK]

Hint: Use COUNTA to count all cells with any data. [OK]

Common Mistakes:

• Using COUNT instead of COUNTA for non-empty cells.
• Typing invalid function names like COUNTALL or COUNTNONEMPTY.
• Forgetting parentheses in function calls.

3. Given the range A1:A5 contains: 10, "apple", 20, "", 30, what is the result of =COUNT(A1:A5)?

medium

A. 4

B. 5

C. 2

D. 3

Solution

1. Step 1: Identify which cells COUNT counts

   COUNT counts only numeric values. The cells with numbers are 10, 20, and 30.

2. Step 2: Count the numeric cells

   There are 3 numeric cells in the range.

3. Final Answer:

   3 -> Option D

4. Quick Check:

   COUNT counts numbers only = 3 [OK]

Hint: COUNT ignores text and blanks, counts numbers only. [OK]

Common Mistakes:

• Counting text cells as numbers.
• Counting empty cells.
• Confusing COUNT with COUNTA.

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

medium

A. You need to use COUNTBLANK instead.

B. COUNT only counts numeric cells, so text cells are ignored.

C. The range B1:B6 is empty.

D. The formula syntax is incorrect.

Solution

1. Step 1: Understand COUNT behavior

   COUNT counts only numeric cells, so if there are text or other data types, they are not counted.

2. Step 2: Identify why count is less than expected

   If you expected 5 but got 2, likely 3 cells have text or other non-numeric data.

3. Final Answer:

   COUNT only counts numeric cells, so text cells are ignored. -> Option B

4. Quick Check:

   COUNT counts numbers only, text ignored [OK]

Hint: Use COUNTA to count all non-empty cells including text. [OK]

Common Mistakes:

• Assuming COUNT counts all non-empty cells.
• Using COUNTBLANK instead of COUNTA.
• Thinking formula syntax is wrong when it is correct.

5. You have a list in C1:C8 with numbers, text, and some empty cells. You want to find how many cells have data (numbers or text) but exclude empty cells. Which formula gives the correct count?

hard

A. =COUNTA(C1:C8)

B. =COUNTBLANK(C1:C8)

C. =COUNT(C1:C8)+COUNTBLANK(C1:C8)

D. =COUNT(C1:C8)

Solution

1. Step 1: Understand the goal

   You want to count all cells with data, including numbers and text, but exclude empty cells.

2. Step 2: Choose the right function

   COUNTA counts all non-empty cells regardless of data type, which matches the goal.

3. Step 3: Check other options

   =COUNT(C1:C8) counts only numbers, so it misses text. =COUNT(C1:C8)+COUNTBLANK(C1:C8) adds numbers and blanks, which counts empty cells incorrectly. =COUNTBLANK(C1:C8) counts only blanks, which is opposite of the goal.

4. Final Answer:

   =COUNTA(C1:C8) -> Option A

5. Quick Check:

   COUNTA counts all non-empty cells [OK]

Hint: Use COUNTA to count all cells with any data, ignoring blanks. [OK]

Common Mistakes:

• Using COUNT and missing text cells.
• Adding COUNT and COUNTBLANK which counts blanks wrongly.
• Using COUNTBLANK instead of COUNTA.