XL: COUNTIF/SUMIF Wrong If Cell Has Single Apostrophes

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0, 7.0a
• Microsoft Excel for the Macintosh, version 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, when you use the COUNTIF and SUMIF functions, you may receive incorrect results if cells included in the range argument contain an apostrophe.

CAUSE

This problem occurs because COUNTIF and SUMIF functions may consider apostrophes in cells of the range argument as matching the criteria argument when that argument is a value.

If the criteria argument is entered as a value without quotation marks, then cells containing only an apostrophe that are directly below a cell that matches the value are considered matching the criteria as well. In addition, cells containing an apostrophe that are below a cell that matches the criteria, but have intervening cells (that is, either blank cells or cells containing text) are considered matching. Other cells with a single apostrophe are not counted or summed. Any cell with a value in it that is interposed between a matching cell and the cell containing the apostrophe prevents that cell from being counted.

If the criteria is entered with quotation marks around the value, all cells with single apostrophes are counted as fitting the criteria.

The problem does not exist if any characters are to the right of an apostrophe.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. The problem is corrected in both Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.

WORKAROUND

To avoid this problem, do not use apostrophes in ranges to be used with the SUMIF or COUNTIF function.