SUMIF() Function Fails If Sum_Range Contains Links to Text (129423)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
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
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"
| Modification Type: | Minor | Last Reviewed: | 8/15/2005 |
|---|
| Keywords: | kbbug kbnofix KB129423 |
|---|
|