Microsoft KB Archive/129423

= SUMIF Function Fails If Sum_Range Contains Links to Text =

Article ID: 129423

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q129423





SYMPTOMS
If you use the SUMIF 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: You must enter the above formula as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.



STATUS
Microsoft has confirmed this to be a problem in the products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.



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.

