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.