formula autocorrecting itself - how to stop it?

[ms_soton]

I have entered a formula into excel which I have used in the past successfully. However, once I press return or click on another cell, the formula automatically updates the range of cells incorrectly.

For example, In cell D2 I entered:

=SUMIF($A$1:$XFD$1,"*q_yourweight_6mths.fp_pounds *",A2:XFD2)

and it changes to this:

=SUMIF($1:$1,"*q_yourweight_6mths.fp_pounds *",2:2)

Could anybody help me stop it doing this? Thank you.

 

[Joe4]

Why is that a problem?

XFD is the last possible column on the sheet. So any range that starts in column A and ends in column XFD is really just selecting the entire row.

So range "$A$1:$XFD$1" and "$1:$1" are really the same thing.

 

[ms_soton]

Ah, I see now! Thank you so much for your help