COUNT and COUNTA functions in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Counting Numbers vs Non-Empty Cells

Given the range A1:A6 contains the values: 10, "", 5, "Text", 0, TRUE.

What is the result of =COUNT(A1:A6)?

A 4

B 3

C 5

D 6

💡 Hint

COUNT counts only cells with numbers.

COUNT counts only numeric values. The numbers are 10, 5, and 0, so the result is 3.

📊 Formula Result

intermediate

Counting Non-Empty Cells

Given the range B1:B5 contains: "Apple", "", 25, FALSE, "Banana".

What is the result of =COUNTA(B1:B5)?

A 4

B 2

C 5

D 3

💡 Hint

COUNTA counts all non-empty cells, including text and logical values.

COUNTA counts all cells that are not empty. "Apple", 25, FALSE, and "Banana" are counted, total 4.

❓ Function Choice

advanced

Choosing the Right Function for Counting Text

You want to count how many cells in the range C1:C7 contain text, but ignore blanks and logical values.

A =COUNTIF(C1:C7,"*")

B =COUNTA(C1:C7)

C =COUNTIF(C1:C7, "<>")

D =COUNT(C1:C7)

💡 Hint

COUNT counts only numbers. COUNTA counts all non-empty cells including logical values. COUNTIF with "*" counts cells with text.

COUNTIF with "*" counts cells containing text only, ignoring blanks and logical values. This matches the requirement.

❓ data_analysis

advanced

Analyzing Mixed Data with COUNT and COUNTA

In the range D1:D8, the cells contain: 12, "", "Hello", 0, TRUE, FALSE, " ", and 7.

What is the difference between =COUNTA(D1:D8) and =COUNT(D1:D8)?

A COUNTA returns 6; COUNT returns 3

B COUNTA returns 8; COUNT returns 4

C COUNTA returns 7; COUNT returns 3

D COUNTA returns 7; COUNT returns 4

💡 Hint

COUNTA counts all non-empty cells including spaces and logical values. COUNT counts only numbers.

COUNTA counts all except the empty cell (""), so 7. COUNT counts numbers: 12, 0, 7, so 3.

🎯 Scenario

expert

Using COUNT and COUNTA in a Sales Report

You have a sales report in range E1:E10. Some cells have numbers (sales amounts), some have text notes, some are blank, and some have the logical value FALSE.

You want to find how many sales amounts were recorded (numbers) and how many entries (numbers or text) were made in total.

Which pair of formulas will correctly give these counts?

A Sales count: <code>=COUNT(E1:E10)</code>, Entries count: <code>=COUNTIF(E1:E10, "<>")</code>

B Sales count: <code>=COUNTA(E1:E10)</code>, Entries count: <code>=COUNT(E1:E10)</code>

C Sales count: <code>=COUNTIF(E1:E10, ">0")</code>, Entries count: <code>=COUNTA(E1:E10)</code>

D Sales count: <code>=COUNT(E1:E10)</code>, Entries count: <code>=COUNTA(E1:E10)</code>

💡 Hint

COUNT counts numbers, COUNTA counts all non-empty cells including text and logical values.

COUNT counts numeric sales amounts. COUNTA counts all entries including text notes. This matches the requirement.