Excel - Sequentialy Count The Number Of Repeat Occurances

I a column in Excel I had to put in a sequence number for each matching value in another column. So if my column of values had the series (Red, Red, Blue, Red Blue) then I would end up with (1,2,1,3,2) for the number sequence.

The values that I was sequential numbering were in column D. This is the formula that I used:

=COUNTIF($D$1:D1, D1)

I just entered the formula in the first cell and then filled the remainder of the cells using the fill handle.

Here is a sample of my sequential count in Excel: