Microsoft

The scoop on Excel's counting functions

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!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
ziffdavis
ziffdavis

Don't forget: SUBTOTAL(function_num, ref1, ref2, ...) Where Function_num = 2 for Count and =3 for CountA The Subtotal function is handy because it does NOT count data in hidden rows/columns, e.g. when you are using a Filter

rcstan
rcstan

Did you intend SCOOP in lieu of SCOPE?

cralph
cralph

You forgot one, COUNTIF([range],[test value]). Great for counting the number of a specific value (text or number) that occurs in a range of cells. (edited: Damn, didn't see the above comment until after I'd posted)

Vthanki
Vthanki

Another important function "COUNTIF" does give you specific string or number count which is more useful than COUNT or COUNTA.

ssharkins
ssharkins

No, I meant scope, but does scoop make more sense? I was thinking more along the lines of discovery not mystery, but maybe scoop would be better?

stapleb
stapleb

I show people how to use COUNT, COUNTA and COUNTBLANK. I know COUNTA and COUNTBLANK give the correct response, but one that the user may not believe. If someone has entered a space into a cell, it does not show, but will be included in the COUNTA and left out in the COUNTBLANK. To our eyes the answer is not correct, but because of the space, the answers are absolutely right.

Hawklord99
Hawklord99

Can't take any credit for this one, but John Franco at: www.Excel-Spreadsheet-Authors.com Opened my eyes with "The Excel SUMPRODUCT Quick Start Guide" which demonstrates not only COUNT functions but SUMs, etc. also using SUMPRODUCT

npjadhav10
npjadhav10

One more function is countifs. Only difference with countif is it takes care of multiple conditions.

ssharkins
ssharkins

Yes, I haven't forgotten COUNTIF() -- I just think it deserves an entry all its own! Look for it soon! But, I probably should've said that in the blog -- thanks for bringing it up!

oldbaritone
oldbaritone

Scope: range of operation, as the scope of a logical operator Scoop: information especially of immediate interest Sounds more like the latter to me

ragdyer
ragdyer

XL's (terminology) inconsistency: Enter ="" in A1 In B1 enter =Countblank(A1) And in C1 enter =Isblank(A1) Might be a little confusing ... Regards, RD

Editor's Picks