The scoop on Excel's counting functions.

Takeaway: Excel offers three counting functions, so it’s important to know each function’s specific purpose and expectations.

As you can imagine, Excel is all about the values. Sometimes that means complex number crunching. Often, it’s just a simple task of summing or counting. Where counting is concerned, Excel offers three functions: COUNT(), COUNTA(), and COUNTBLANK(). Regardless of your counting tasks, one of these functions should meet your needs.

COUNT()

Excel’s COUNT() function counts only the numbers in a range. This function uses the following forms:

COUNT(v1, v2[, …])

COUNT(range)

where v1 and v2 represent the literal values you want to count and range identifies a range of cells whose contents you want to count.

This function counts only numbers. Cells can contain any type of data, but the function counts only numbers, including dates. The function ignores empty cells, logical values, text, and error values.

In the following figure, the COUNT() function in cell D12, COUNT(D2:D10), returns the value 7. Although D2:D10 comprises nine cells, only seven contain valid numbers. The string N/A in D6 is a string, not a number and cell D8 is blank. The function does count 0 in cell D7.

COUNT() is straightforward. It’s easy to understand and implement, but it isn’t always adequate.

COUNTA()

When you need to count all values, not just number, use COUNTA(). This function uses the same forms as COUNT() but be careful because this function considers all values–even the ones you can’t see.

Notice that the COUNTA() function in D13 returns 9, even though D8 appears empty. Did you expect the function to return 8? Cell D8 actually contains a formula, =”", which returns an empty string. Consequently, the cell isn’t truly empty and every cell in D2:D10 contains a value. If you remove that formula, COUNTA() would return 8.

COUNTBLANK()

COUNTBLANK() counts empty cells in a specified range of cells. Unlike the other two count functions, this function takes only one form:

COUNTBLANK(range)

which makes sense—you can’t really enter a blank as an argument.

Now, here’s where things get a bit sticky. COUNTA() counts cells that contain a formula that returns an empty string. Since COUNTBLANK() is counting blanks, you might expect it to not count cells that contain formulas that return an empty string—but it does. The COUNTBLANK() function in cell D14 returns 1 because it treats the =”" formula in cell D8 as a blank cell.

Next time you need to count something, consider all three counting functions and make an informed decision!