COUNT vs COUNTA vs COUNTBLANK, Which Counting Function to Use

"How many people have filled in their hours?" sounds like a one-formula question. Then COUNT says 4, COUNTA says 8, and you can see 9 rows on the screen. None of the formulas is broken. Each one counts a different kind of cell, and once you know which kind, the numbers stop disagreeing.

1. What each function counts

Microsoft's documentation describes the three functions like this:

• COUNT counts cells that contain numbers. Dates count too, because Excel stores them as numbers. Inside a range, empty cells, TRUE or FALSE, text, and error values are skipped.
• COUNTA counts cells that are not empty. Microsoft is specific that this includes error values and empty text (""), such as a formula that returns an empty string.
• COUNTBLANK counts empty cells. Microsoft notes that cells with formulas returning "" are counted as blank, while cells containing zero are not.

Google's help pages describe the same behavior in Sheets: COUNTA counts text values "including zero-length strings and whitespace," and COUNTBLANK treats cells with no content and cells containing "" as blank.

2. A timesheet where the counts disagree

Here is a small hours column in B2:B10:

Fay's cell holds =IF(C7="","",C7), a common pattern that shows nothing until another cell is filled in. Hal's cell looks empty but contains one space character, the kind of thing that arrives when data is pasted from another system.

Entered in Excel, the three functions return:

=COUNT(B2:B10)
=COUNTA(B2:B10)
=COUNTBLANK(B2:B10)

• COUNT: 4. Only Ana, Ben, Gus and Ivy have real numbers. The 0 counts, because zero is a number. Eli's 8 does not, because it is text.
• COUNTA: 8. Every cell except Cho's has something in it, including Dev's text, Fay's empty-text formula and Hal's space.
• COUNTBLANK: 2. Cho's empty cell and Fay's formula. Hal's space is not blank.

Add the last two together and you get 10, from a range of only 9 cells (=ROWS(B2:B10) returns 9). Fay's cell is counted by both COUNTA and COUNTBLANK, which is exactly what both help pages describe. If you ever build a check like "filled plus blank should equal total rows," a formula returning "" will quietly break it.

3. Why COUNT is lower than you expect

When COUNT is lower than the number of values you can see, something in the column is not a number. In this sheet, the difference is useful:

=COUNTA(B2:B10)-COUNT(B2:B10)

This returns 4: the non-numeric entries Dev, Eli, Fay and Hal. It is a quick way to find out whether a column you plan to total has text mixed in. Here, =SUM(B2:B10) returns 17.5, not 25.5, because SUM also skips Eli's text 8.

Microsoft's COUNT page adds one twist. Values typed directly into the formula are treated differently from values in cells. =COUNT("1",TRUE) returns 2, because a number in quotes and a logical value are counted when you type them as arguments, even though the same values sitting in cells would be ignored.

4. Counting cells that truly contain something

If "filled in" should mean "has visible content," neither COUNTA nor COUNTBLANK gives that answer here, because of Fay's formula and Hal's space. This formula checks each cell after trimming spaces:

=SUMPRODUCT(--(LEN(TRIM(B2:B10))>0))

It returns 6: Ana, Ben, Dev, Eli, Gus and Ivy. TRIM removes the space in Hal's cell, LEN measures what is left, and the double minus turns each TRUE or FALSE into 1 or 0 so SUMPRODUCT can add them.

5. Counting people who left the cell empty

To list how many named people have no entry, test both columns:

=COUNTIFS(A2:A10,"<>",B2:B10,"")

This returns 2 (Cho and Fay). The "<>" condition means "not empty," and "" means "empty." Like COUNTBLANK, the empty condition matches the formula returning "". A related check, =COUNTIF(B2:B10,"<>"), returns 8, the same as COUNTA.

6. Which one to use

When two counts disagree, do not assume one is wrong. Click the odd cells and look at the formula bar: a space, a text number, or an empty-text formula is almost always the reason.

7. Key takeaways

• COUNT counts only numbers and dates; text, logical values and errors in cells are skipped.
• COUNTA counts every non-empty cell, including errors, spaces and formulas that return "".
• COUNTBLANK counts empty cells and "" formulas, but not zeros or spaces.
• A formula returning "" is counted by both COUNTA and COUNTBLANK, so the two can add up to more than the row count.
• COUNTA minus COUNT is a quick test for text hiding in a number column.