Microsoft KB Archive/192728

= XL98: SUMIF Function Fails If Sum_Range Contains Links to Text =

Article ID: 192728

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



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

=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.

