Take a look in Excel's help for relative and absolute addressing.

Your formula:
=(((COUNTIF(J9,"=x"))*J6)*J7)+(((COUNTIF(K9,"=x")) *K6)*K7)
+(((COUNTIF(L9,"=x"))*L6)*L7)

Could be re-written as:

=(((COUNTIF(J9,"=x"))*J$6)*J$7)+(((COUNTIF(K9,"=x" ))*K$6)*K$7)
+(((COUNTIF(L9,"=x"))*L$6)*L$7)

and the addresses with the $ in the rows won't change row numbers.

If you use
$a1
then copy that formula, column A won't change (the row would adjust to where you
pasted the cell)

$a$1 would never change (when you copy the cell).

galiant wrote:

Is there a way to edit replace values in a formula easily. I have a very
complex formula that reads ranges of cells A1:A33,"BOB", but my formula looks
at about 60 ranges to get the final sum. So when I want to use the same
formula for "TOM" I have to edit all 30 instances of "bob" to "tom" in the
formula. Is there a way to edit/replace in the formula or a better way to
handle this?

It would also be helpful, because in some of my formulas I want to change
the ranges for all 30 instances in this extremely long formula, but I have to
edit all 30 of them. Based on location, if I just copy and paste the ranges
get messed up.

For example in this formula:
=(((COUNTIF(J9,"=x"))*J6)*J7)+(((COUNTIF(K9,"=x")) *K6)*K7)+(((COUNTIF(L9,"=x"))*L6)*L7)

The next row down would be:
=(((COUNTIF(J10,"=x"))*J6)*J7)+(((COUNTIF(K10,"=x" ))*K6)*K7)+(((COUNTIF(L10,"=x"))*L6)*L7)

the first value after COUNTIF(___ changes but the rest of the formula stays
the same. I am counting in that row, but based on a fixed cells in row 6.

--

Dave Peterson