Drag copy SUMIFS just repeats my cell values (manual entering is fine)

[Deleted]

I am trying to drag copy (well double clicking the '+' to copy actually) the following SUMIFS functions:

=SUMIFS(roadtransco15!$D$2:$D$146729,roadtransco15!$A$2:$A$146729,A2,roadtransco15!$B$2:$B$146729,B2)

=SUMIFS(roadtransco15!$D$2:$D$146729,roadtransco15!$A$2:$A$146729,A3,roadtransco15!$B$2:$B$146729,B3)

=SUMIFS(roadtransco15!$D$2:$D$146729,roadtransco15!$A$2:$A$146729,A4,roadtransco15!$B$2:$B$146729,B4)

The function works just fine, but when I drag copy it just repeats the above 3 lines over and over.

What can I do to resolve this?

Many thanks!

[#2]

Hi Christopher

Hopefully I am understand you right. Maybe try this?

Go to your first cell, click once into the cell, move the mouse to the bottom right corner of the cell until you see +. Left click, hold and drag downwards.

This should hopefully fix the problem.

I also notice that your formula is changing as you copy it down so it might be worth having another look to see if the cells like A and B are changing as you copy it.

Please let me know if you still have any issues. Maybe add a screen shot or sample file if this fix doesn't work?

Good luck!

Cheers
Damien

[#3]

Thanks Damien, no it's not this simple.

The 3 formulae shown are how I want the pattern to be.

Drag copy with either method is not extending the pattern, moreover it is just repeating those three lines exactly as typed.

If I try with only one cell, the first formula, the same thing happens, just that one value repeated thousands of times!

Or so I thought!
SOLVED.
It has been doing it correctly all along. The strange behaviour seemed to be a placeholder while Excel correctly calculated the several hundred thousand records.

I actually had to do an undo and a redo to spot this and enforce the update (of the cell values shown).

[#4]

Awesome Christopher. Glad to hear it resolved!

Best wishes
Damien