Excel 2007 Report Number of Occurrences in Second Column

B5:B53 Is a list of values I will be pasting into regularly.

In C5:C53 I would like to count the occurrences of the item.

SO:

Apple      1
Banana     1
Orange     1
Apple      2
Plum       1
Orange     2
Apple      3

I just can't seem to get the COUNTIF (think that's what should do it) to work.

Then when I go back in and paste another list into that B column range the C column will update.

Solution

You need to make the range dynamic. The first cell in the range is fixed while the second is relative:

=COUNTIF($A$1:A1,A1)

So as it is dragged/copied down the second part of the range will change thus always looking from the cell up.