Copying formulas without changing the range
 

I use a lot of Countif formulas. When I copy the formula from one worksheet
to another, or from one row to another, it changes the range (i.e. J6:J69
becomes J7:J70, etc.) I want to maintain the same range (J6:J69) but cannot
get it to do that. Does anyone know how I can keep the range the same in
subsequent rows? I just want to change the criteria of the count, not the
range.

Copying formulas without changing the range
 

Make the criteria range absolute.

Three ways to do this

In the formula box select J6, and press <F4 It will change to $J$6. Now
select J69, and repeat, so that it changes to $J$69.

In the Formula box, Type "$"'s before J, 6, J and 69

Select the range J6:J69, and give it a range name. To do this, after
selecting the range, click in the address box - it will show J6 - and type in
a name, such as CritRange, and press <Enter

Especially in other sheets, the latter will work well.

You can now cpy this formula to your heart's content, and it will always
refer to CritRange, or then to $J$6:$J$69!

--
Hth

Kassie Kasselman
Change xxx to hotmail


"gaelicamethyst" wrote:

I use a lot of Countif formulas. When I copy the formula from one worksheet
to another, or from one row to another, it changes the range (i.e. J6:J69
becomes J7:J70, etc.) I want to maintain the same range (J6:J69) but cannot
get it to do that. Does anyone know how I can keep the range the same in
subsequent rows? I just want to change the criteria of the count, not the
range.

Copying formulas without changing the range
 

Use absolute addressing, as in:

=countif($J$6:$J$69,"something")

The $ tells Excel not to adjust the address on copying.

Regards,
Fred.

"gaelicamethyst" wrote in message
...
I use a lot of Countif formulas. When I copy the formula from one
worksheet
to another, or from one row to another, it changes the range (i.e. J6:J69
becomes J7:J70, etc.) I want to maintain the same range (J6:J69) but
cannot
get it to do that. Does anyone know how I can keep the range the same in
subsequent rows? I just want to change the criteria of the count, not the
range.