ID: Q150369
The information in this article applies to:
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.
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.
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.
To avoid this problem, do not use apostrophes in ranges to be used with the SUMIF or COUNTIF function.
Additional query words: 5.00 5.00a 5.00c 5.0 5.0c 7.00 7.00a
Keywords : xlformula
Version : WINDOWS:5.x,7.0,7.0a; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbugLast Reviewed: February 5, 1998