automate replace values in formula

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.

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

Instead of putting what you want to match in the formula, use a cell
reference. they you can either change the what is in the cell, or have a
colomnor row that has the list.
As an example, In one sheet I have the following three columns:
Airlines flown are in column D, miles flown in F.
The table J through L, with the list of airlines in J

Aero Peru =COUNTIF(D:D,J4) =SUMIF($D$4:$D$506,J4,$F$4:$F$506)
Aerolineas =COUNTIF(D:D,J5) =SUMIF($D$4:$D$506,J5,$F$4:$F$506)
Air Aruba =COUNTIF(D:D,J6) =SUMIF($D$4:$D$506,J6,$F$4:$F$506)
AA =COUNTIF(D:D,J7) =SUMIF($D$4:$D$506,J7,$F$4:$F$506)
Bahamas =COUNTIF(D:D,J8) =SUMIF($D$4:$D$506,J8,$F$4:$F$506)
Braniff =COUNTIF(D:D,J9) =SUMIF($D$4:$D$506,J9,$F$4:$F$506)



"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.
>
>
>