Microsoft KB Archive/215732

= XL2000: Formula Using SUMIF Worksheet Function Does Not Recalculate Sum =

Article ID: 215732

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q215732



SYMPTOMS
When you create a formula in Microsoft Excel that uses the SUMIF worksheet function, and you then change one of the values that you want to sum, the formula does not return an updated sum as expected.



CAUSE
This issue can occur if the range that is evaluated for the criteria and the sum_range are not the same length, and the value that you change is outside of the range of the sum_range but inside the range of the cells being evaluated, as in the following example:   Start Excel, and then create the following worksheet.   A1: 3  B1: 4 A2: 3 B2: 5 A3: 3 B3: 6 A4: 3  In cell C4, type the following formula:

=SUMIF(A1:A3, A4, B1:B2)

With this formula, you evaluate the range of cells A1:A3 using the criteria in cell A4. The range of cells that you want to sum is specified as B1:B2, which is a different length than the range of cells that you are evaluating. Excel automatically extends the sum_range to include B3 and returns a value of 15. Change the value in cell B3 to 7, and then press F9 to recalculate. B3 is not specified in the formula, therefore the recalculate command doesn't work as expected.



RESOLUTION
To resolve this issue, change the formula so that the criteria range and the sum_range are the same length.

In the example shown in the "Cause" section, change the formula to read as follows:

=SUMIF(A1:A3, A4, B1:B3)

-or-

=SUMIF(A1:A2, A4, B1:B2)



WORKAROUND
To work around this issue, change or delete and then re-enter a value in the specified sum_range.

In the example shown in the "Cause" section, after performing step 3, delete the value in B1, and then re-enter the value 4. The formula recalculates and then returns the correct value of 16.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

