XL98: SUMIF() Function Fails If Sum_Range Contains Links to Text (192728)
The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q192728 SYMPTOMS
If you use the SUMIF worksheet function and if the range you include for
the sum_range argument contains links to text or formulas that evaluate to
text, the function may return an incorrect result.
RESOLUTION
To work around this problem, use a combination of the SUM and IF functions
nested together in an array formula. For example, instead of using the
following
use the following:
=SUM(IF(B2:B6=2,A2:A6,0))
NOTE: The above formula must be entered as an array formula. To enter a
formula as an array formula, press COMMAND+RETURN.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel 98
Macintosh Edition.
MORE INFORMATION
The SUMIF function uses the following syntax: =SUMIF(range, criteria,
sum_range). Any cell in the sum_range that contains a link to text causes
this problem when a cell containing the value in the "criteria" argument is
found in the same row as a cell in the sum_range that contains the link to
text. Under this condition, SUMIF returns the "criteria" value instead of
the corresponding value from the sum_range.
REFERENCES
For more information about entering and editing array formulas, click
Contents And Index on the Help menu (or on the Balloon Help menu if you
are using a version of the Macintosh operating system earlier than 8.0),
click the Index button in Microsoft Excel Help, type the following text
array formulas, entering and editing
and then click Show Topics. Select the "About array formulas and how to
enter them" topic, and click Go To. If you are unable to find the
information you need, ask the Office Assistant.
| Modification Type: | Major | Last Reviewed: | 9/11/2002 |
|---|
| Keywords: | kbbug KB192728 |
|---|
|