Search code examples
excelreferenceexcel-formulacountifexcel-2011

How can I make a custom fill handle pattern?

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.

Solution

  • 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”.