Forum Discussion

Deleted's avatar
Deleted
Mar 19, 2018

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

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!

 

Formulas & Functions
SUMIFS

3 Replies

Sort By
  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Mar 19, 2018
    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
    • Deleted's avatar
      Deleted
      Mar 19, 2018

      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).

Resources