XL98: SUMIF() Function Fails If Sum_Range Contains Links to Text

ID: Q192728

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

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

=SUMIF(B2:B6;2;A2:A6)

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.

Additional query words: XL98

Keywords : kbdta xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug