XL: COUNTA Function Returns 1 with Invalid Reference Argument

ID: Q184105

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, version 5.0

SYMPTOMS

If you type an invalid cell reference as an argument for the COUNTA worksheet function, the result is 1. For example the following function returns 1 in A1.

   A1: =COUNTA(B0:b10)

NOTE: The second character of the first cell reference is the number zero.

CAUSE

This problem occurs because the COUNTA function interprets the invalid reference as the #NAME? error value and the function counts this one error value.

RESOLUTION

To resolve this problem and count the number of nonblank cells in a range, type a valid reference for the range in the COUNTA worksheet function.

STATUS

This behavior is by design of Microsoft Excel.

MORE INFORMATION

The COUNTA worksheet function counts the number of cells that are not empty and counts the values in the list of arguments. Nonempty cells can contain text, numbers, or error values. The arguments of the COUNTA worksheet function can also be an array or a list. For example, the following function returns 3 because it counts a text string, a number, and an error value:

   A1: =COUNTA("hello",4,#N/A)

In the following example, the argument of the COUNTA worksheet function is evaluated as #NAME? because cell B0 is not defined, and the result of the COUNTA worksheet function that appears in A1 is 1.

   A1: =COUNTA(B0:B10)

You can see this behavior if you select B0:B10 in the formula bar and press F9 to evaluate the reference. The formula changes to the following:

   A1: =COUNTA(#NAME?)

Excel counts one nonblank item (an error value), and the answer is exactly 1.

NOTE: Use the COUNT worksheet function to count numbers only.

REFERENCES

For more information about the COUNTA worksheet function, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

   COUNTA worksheet function

and then double-click the selected text to go to the "COUNTA" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL97 xl95 xl7 xl5

Keywords          : kbdta xlformula xlwin 
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: February 15, 1999