Copying formulas without changing the range

[gaelicamethyst]

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.

 

[Kassie]

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!

 

[Fred Smith]

Use absolute addressing, as in:

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

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

Regards,
Fred.