COUNTIF/SUMIF Wrong If Cell Has Single Apostrophes (150369)
The information in this article applies to:
- Microsoft Excel for Windows 95 7.0a
- Microsoft Excel for Windows 5.0c
- Microsoft Excel for the Macintosh 5.0a
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.
| Modification Type: | Minor | Last Reviewed: | 8/15/2003 |
|---|
| Keywords: | kbbug KB150369 |
|---|
|