How Do You Interpret this Formula?

I got 2 parts to a question:

PART1:
How do you interpret the formula below? The worrkbook in attached. Does B2&COUNTIF mean it adds whatever names are in column B and adds the number of counted names after it?

Please Login or Register  to view this content.

PART 2:
This question has to do with cell referencing, which I'm a little confused about. When I put in B$2 in the first part of the formula which reads as, B2&COUNTIF($B$2:B2,B2), just the name Robert Acey comes up when I paste down the column. Does this mean the values in the columns can increment but those in the rows stay fixed?

It reads as: the contents of cell B2 and the number of times that same value has appeared. Robert Acey1, Robert Acey2, etc.

In regards to referencing, when you put a dollar sign ($) in front of either the column or row, it stays fixed. You can lock the column ($A1), the row (A$1), or both ($A$1). When you copy formulas, anything that isn't locked changes based on where the formula goes. Using your formula as an example, I'm going to emulate filling the formula down to five rows:

=B2&COUNTIF($B$2:B2,B2)
=B3&COUNTIF($B$2:B3,B3)
=B4&COUNTIF($B$2:B4,B4)
=B5&COUNTIF($B$2:B5,B5)
=B6&COUNTIF($B$2:B6,B6)

The purpose of locking that first cell is so that you can count how many times something has appeared between the first cell (fixed) and the current cell (dynamic).

The cell at the start of the formula, and after the comma at the end (B2, not locked) tells the formula what to look for. This is the part that shows Robert Acey or Matt Wolf or Dan Brown.

The part that's fixed is tell it what range to look at ($B$2:B2). This part says to count how many times the name on the current row has appeared, between it and the first row.

So on the first line of the formula, it looks from $B$2:B2 (which is only one cell), and returns RobertAcey1 because there's only one instance of the name. On the 9th line of the formula, it looks from $B$2:B9 (which is 8 cells), and returns Robert Acey2 because that's the second time Robert Acey appears.

If you don't make $B$2 fixed, then the count will always be 1 (B2:B2 and then B9:B9, etc).

Ahhhh... Its clear now. Thank you so much my friend. I'm glad youre on this forum!