XL97: COUNTIF Returns Incorrect Value When It Refers to Blank Cells (232494)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q232494

SYMPTOMS

The COUNTIF function may return an incorrect, smaller result when counting blank cells.

CAUSE

This problem occurs when the following conditions are true:
  • The COUNTIF function references a criteria range with blank cells at the right or bottom of the range.

    -and-

  • The function uses "" as the criteria.

    -and-

  • The "used range" of the worksheet, as defined by the last column and last row with data, does not include the blank area of the referred-to criteria range.

WORKAROUND

To work around this problem, type some number or text to the right of and below any ranges used in such formulas. Excel then sees the rectangle starting with A1 to the "last cell," as defined by the column and row of that entry, as being used or "dirtied," and the formulas will work as expected.

STATUS

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

MORE INFORMATION

If the COUNTIF function returns an incorrect smaller value because of this problem, and the workaround is applied, you must cause Excel to completely recalculate before the functions can work correctly. To do this, re-enter the formula or press CTRL+ALT+F9. If data is deleted to create a smaller "used range," you must save the workbook to cause Excel to recognize there is a new "used range," and then recalculate the workbook.

REFERENCES

For more information about COUNTIF, click the Office Assistant, type countif, click Search, and then click to view "COUNTIF."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, click the article number below to view the article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component

Modification Type:MajorLast Reviewed:8/27/2002
Keywords:kbbug KB232494
©2002 Microsoft Corporation. All rights reserved.