Thanks for the reply!

I might have confused things a bit!

My sheet looks like this.

+ A B C D E F G H I J K L M O P.....
1 1 2 3 4 3 3 2
2 8 4 2 5 6 3 5
3 9 3 4 5 2 7 5
4 8 6 7 1........

And repeats with random numbers down the spread sheet for about 900 rows. In column H or I the code I had done =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=1,"1"," it would put in a 1 if it occured in the row. In the next two rows it would be blank (no 1 in those rows). In the 4th row down it has a one but in row H I want it to put in '2' counting the two blank cells above.

It gets worse, in the next column I have =IF(COUNTIF(A7:B7:C7:D7:E7:F7, "1")=2,"1"," so would like this to do the same except obviously for 2.

I think this may be a bit out excels realm but it would be interesting to see if it could!

Thanks,

Jason