The way to do this is add "$" to the formula. The $ acts as an
"anchor" that prevents the automatic shift that occurs when you copy
from column A to column B. For instance:
=SUM($A10:$A20) prevents the column reference from changing, but allows
the row numbers to change.
=SUM(A$10:A$20) prevents the row number from changing but allows the
column reference to change.
=SUM($A$10:$A$20) locks both the column reference and the row
reference, allowing neither to change.

This takes a bit of getting used to, so be careful at first to make
sure it is behaving as you need it to. Also, a hint: when you're
writing or editting the formula, you can press the F4 key a number of
times to apply the anchors.