XL2000: COUNTA Function May Return Incorrect Result of 1

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.

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: NOTE: A cell reference of Al returns a 4 in the preceding formula.

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:

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

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.