Microsoft KB Archive/215276

= XL2000: COUNTA Function May Return Incorrect Result of 1 =

Article ID: 215276

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q215276



SYMPTOMS
In Microsoft Excel, when you use the COUNTA worksheet function in a formula, the formula may return a 1 (one) regardless of how many cells in the referenced range contain data.



CAUSE
This behavior can occur if a formula using the COUNTA worksheet function contains an invalid cell reference, in which case, the formula returns a 1 without any indication of an error within the formula, as in the following example:   Start Excel 2000 and create the following spreadsheet:   A1: 12 A2: 2 A3: 25 A4: A5: 1  In cell B2 type the following formula:

=COUNTA(Al:A5)

Note that the cell A1 is typed as "Al"; that is the number 1 is mistakenly replaced by a lowercase L.

The formula returns a 1 instead of the following Excel error as expected:


 * 1) NAME?

NOTE: A cell reference of Al returns a 4 in the preceding formula.



RESOLUTION
To resolve this issue, ensure that the cell references are valid in any formula using the COUNTA worksheet function equation.

TIP: One way to test the equation is to replace the worksheet function COUNTA with the worksheet function SUM. If there is an invalid cell reference in the equation, the formula returns the following Excel error:


 * 1) NAME?



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

