Sumif & Sort

[nc]

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?

 

[Pete_UK]

Once you have copied the formula down, highlight all the cells with
the formula in and CTRL-H (Find & Replace):

Find what: ation'!A
Replace with: ation'!A$
Click Replace All

Hope this helps.

Pete

 

[nc]

Thanks.

Did you mean

Replace with: ation'!$A


I replaced with the above and it solved the problem.

 

[nc]

Hi Pete

I just realised that when I used

Replace with: ation'!$A

After sorting I get

=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A2,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A5,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A6,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A7,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A11,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A12,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A13,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A14,'Unit-E data'!G:G)
=SUMIF('Unit-E data'!A:A,'Unit-E data consolidation'!$A15,'Unit-E data'!G:G)

for rows 1-10, for criteria was expecting ation'!$A2, ation'!$A3, ation'!$A4
etc.

When I change the sheet reference to just the cell reference A2, A3, A4
etc., the criteria is fine after sorting.