Maintaining Range for formula in excel

Mmmm_Lemony

Not sure if this is the right forum, if not, please feel free to move.

I have a sheet with values listed in two column. Lets say peoples names and a count of those names.
A2...An, with A1 = 'Names'
B2...Bn, with B1 = 'Count'

|.| A | B |
|1| Name | Count |
|2| John | 1 |
|3| Bill | 2 |
|4| Bill | 2 |
|5| Mark | 1 |

As I add names to column 'A' I want to know if a previous entry of the same name exists, and if so how many in total to be populated in 'B'.

So In B2...Bn, I should have the formula;

'=(COUNTIF(A2:A100,A2))', '=(COUNTIF(A2:A100,A3))', ... n

So I am essentially searching the range A2:A100 for the value from A2. If found return the count. This is fine. And works no problem. Except that I have to specify the end point of the range in each formula. I just want to search the entire column. Also, If I select the cell and drag down, the range will increment, which will mean its not searching the entire list.

So instead of;

'=(COUNTIF(A2:A100,A2))', '=(COUNTIF([B]A2:A100[/B],A3))', ... n

It becomes;

'=(COUNTIF(A2:A100,A2))', '=(COUNTIF([B]A3:A101[/B],A3))', ... n

Any ideas how I can keep the formula the same except for the change to the value to search? Ideally I would like to search A1/A2 up to the current cell everytime. I am restricted in that I can't use VBA or macros.

Graham

To fix a cell, put a $ in front of it.

E.g.

$a$3:$a$101

To fix just the column

$a3:$a101

To fix just the row

a$3:a$101

Mmmm_Lemony

Graham wrote: »

To fix a cell, put a $ in front of it.

E.g.

$a$3:$a$101

To fix just the column

$a3:$a101

To fix just the row

a$3:a$101

I had tried this but only in front of the letter not the index so it didn't work. Cheers.

duffman85

You can use A:A to use the entire column as the range:

=COUNTIF(A:A,A2)