XL2000: COUNTA Function May Return Incorrect Result of 1 (215276)


The information in this article applies to:

  • Microsoft Excel 2000
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:
  1. Start Excel 2000 and create the following spreadsheet:
       A1: 12
   A2:  2
   A3: 25
   A4:    
   A5:  1
  2. 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:

#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:

#NAME?

STATUS

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

REFERENCES

For more information about the COUNTA() worksheet function, click Microsoft Excel Help on the Help menu, type counta in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kbpending KB215276
©2003 Microsoft Corporation. All rights reserved.