I wrote a simple sumif function in a column and copied down the range,

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!A2,'Unit-E data'!G:G)


That was fine, but when I tried to sort the range I noticed that criteria
(cell reference) changed from A2 to something totally different for the row 2.

Is there any way to get round this problem?