HI, I am using Excel 2003
This is my sum if formula.
SUMIF('Sheet1'!$B:$B,'Sheet2'!$B5,'Sheet1!$W:$W)

My criteria, 'Sheet2'!$B5, is text and has values that are 22913, 12475, 0000022913, etc. All values are numeric, but are text. Note that some of the values have leading zeroes. So 22913 is different than 0000022913. The problem I am having is that say when 'Sheet2'!$B5 is 22913, the value 0000022913 in the range 'Sheet1'!$B:$B is seen as true and it will add in the corresponding value in the sum range 'Sheet1!$W:$W. I was able to solve this by surrounding the criteria with double quotes: SUMIF('Sheet1'!$B:$B,"'Sheet2'!$B5",'Sheet1!$W:$W)

However I need to copy down this formula, but when I do so the criteria remains $B5, not updating to $B6 in the next cell.

Thanks