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

ID: Q129423

The information in this article applies to:

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

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.

REFERENCES

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.

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

Additional query words: 5.00a 5.00c 8.00 97 98 XL97 XL7 XL95 XL5

Keywords : kbdta xlformula
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbug