countif formula changes cell values and cell reference is unchanged

Hi all,

Some times get stuck on basics.. always learning.

iF i FILL IN FORMULA IN sheet2 B2

=COUNTIF(Sheet1!A2:N20,"a")

AND DRAG IT DOWN THE CELLS CHANGE

=COUNTIF(Sheet1!A3:N21,"a")

AND SO ON.

Secondly when i drag it down the adjacent cell reference is frozen to "a" where as it should change to b , c,, and so on.

is THERE ANY WAY I CAN DO IT EASILY.

many thanks

Kind regards

Are you saying that you don't want the cell references to change? If so, you can do this:

=COUNTIF(Sheet1!A$2:N$20,"a")

the $ symbol in front of the row reference prevents it from changing when the formula is copied down. You can do a similar thing in front of the column reference if you copy across.

It might look better if you put a, b, c etc in cells A2, A3, A4 in Sheet2, then your formula in B2 will become:

=COUNTIF(Sheet1!A$2:N$20,A2)

When this is copied down the A2 at the end will change to A3, A4 etc. on subsequent rows, so it will be looking for the values that are in those cells.

Hope this helps.

Pete

Hi Kaper,
The formula works fine. Thanks for your kind help.
I have clicked * to express my gratitude.
Kind regards

Hi martindwilson,
I agree with you.
Really thanks for your great help.
Many solutions for this.
I have clicked * to thanks you.
Kind regards
KK1234

Hi Pete,
That's great.
Spent a lot of time on this.
But finally it is solved with your help.
Thanks again. It works great.
I have clicked * to thank you.
Kind regards
KK1234