2

I have a column A3:A71 I wish to populate with values

=COUNTIF(B3:B71,B3)

Where the second argument is incremented with every cell. Obviously I don't want to copy this function every time, so I was hoping that fill handle would help me. However although it correctly increments the second argument of COUNTIF, it also increments the first one. Even if I correctly populate the first two or three cells in the column A3:A71 with the values 

=COUNTIF(B3:B71,B3)
=COUNTIF(B3:B71,B4)
=COUNTIF(B3:B71,B5)

when I drag down from the bottom right corner, I get the function:

=COUNTIF(B6:B74,B6)
=COUNTIF(B6:B74,B7)
=COUNTIF(B6:B74,B8)

=COUNTIF(B9:B77,B9)
=COUNTIF(B9:B77,B10)
=COUNTIF(B9:B77,B11)

Can anybody please tell me how can I force the first argument to stay the same while the second one is increased correctly?

I am using MS Office 2011 for MacOS, but a Windows solution would be just as helpful.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2565010
  • 1,876
  • 4
  • 23
  • 37

1 Answers1

2

Please try: 

 =COUNTIF(B$3:B$71,B3)

There are further details at OwenBloggers.com including a table:

SO28445161 example

and mention that other terms are “absolute cell reference” and “locking”.

pnuts
  • 58,317
  • 11
  • 87
  • 139