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

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

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)

   =SUM(IF(B2:B6=2,A2:A6,0))

STATUS

Microsoft has confirmed this to be a problem in the products listed at the beginning of this article.

MORE INFORMATION

Examples of the "{=SUM(IF())}" type of formula construction can be found in the Microsoft Excel 5.0 Samples file in your Excel directory in cells C14:E16 on the Worksheet Functions tab. In the Windows environment, the file is named Samples.xls. On the Macintosh, it is named Microsoft Excel 5.0 Samples.

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.

For more information about array formulas, see "Microsoft Excel User's Guide," Chapter 10, "Working with Arrays"