Calculation is off with SUMIF...why?

[3G]

Hi there-

I have a SUMIF that I copied down a few cells. The sum range of the first was A5:A871 (criteria range C5:C871), which I forgot to "$" to freeze. So, when I copied it down, it went from A6:A872, and, A7:A873. Cells A872 & A873 were both blank, and, the SUMIF criteria was NOT met in the C:6 or C7, yet my calculation was SOOOOOO off (actual value was 9500, but, mis-copied value was 39480!?!).


why is this? The criteria was not met, and the values were 0 anyway?

 

[Hui]

Please post the formula

 

[3G]

Original

=SUMIF($D$5:$D$871,D890,L5:L871)

Pasted down where the values were way off

=SUMIF($D$5:$D$871,D891,L6:L872)

=SUMIF($D$5:$D$871,D892,L7:L873)


Working fine version (s)

=SUMIF($D$5:$D$871,D890,$L$5:$L$871)

=SUMIF($D$5:$D$871,D891,$L$5:$L$871)

=SUMIF($D$5:$D$871,D892,$L$5:$L$871)

 

[Luke M]

Sample data for your formula:

A___B

1___2

1___3

2___4

(blank row)


If original SUMIF was:

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

this results to 5. However, if it got changed to:

=SUMIF(A1:A3,1,B2:B4)

it would evaluate to 7. This is because the formula says "the first 2 values in criteria range meet criteria, so give me first 2 values from sum range (3 and 4).


Does that help?