Hi Shon

Try the below formula in cell C1 and copy down as required....The formula
will return the count of unique items aganist the first item which appears in
ColA as below

=IF(ISERROR(OFFSET(A1,-1,)),COUNTIF(A:A,A1),IF(OR(A1="",
OFFSET(A1,-1,)=A1),"",COUNTIF(A:A,A1)))


Col A Col C
AAA 3
AAA
AAA
BBB 1
CCC 4
CCC
CCC
CCC
DDD 1


--
Jacob


"Shon" wrote:

Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD