Microsoft KB Archive/150369

= COUNTIF/SUMIF Wrong If Cell Has Single Apostrophes =

Article ID: 150369

Article Last Modified on 8/15/2003

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q150369



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.

Additional query words: 5.00a 5.00c 5.0 7.00a XL

Keywords: kbbug KB150369

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.