Article ID: 231000 - View products that this article applies to.
This article was previously published under Q231000
This article has been archived. It is offered "as is" and will no longer be updated.
The COUNTIF function may return an incorrect, smaller result when counting blank cells.
This problem occurs when the following conditions are true:
To work around this problem, type some numbers 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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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 that there is a new "used range," and then recalculate the workbook.
For more information about COUNTIF, click Microsoft Excel Help on the Help menu, type COUNTIF worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.