Count how often value changes in a column, ignoring blank cells

[flyaway]

Hi,

I am trying to construct a formula which counts how often the value in a column changes within a time period, disregarding blank cells.
Basically, there is an event on day D and I want to know how many times the value in another column switched from A to B (or v.v.) since D-15. The tricky part here is that there are also days with blanks.

I have tried to do this using sumproduct, but in that case a change from an empty cell to a non-empty cell was also counted. The only way I have managed to obtain a result is by filtering out the blank cells, then copying and pasting the range elsewhere, applying =IF(A1<>A2,1,0), and then making a summation of that over the past 15 days. Since I have to count the value for hundreds of worksheets it would really help if there was less cumbersome way to do it.

You will find a before file in attachment to help explain what I'm looking for.

I would very much appreciate your help.

Thanks!

[:) Sixthsense :)]

It is not possible using worksheet functions

You have to go for VBA approach with the use of worksheet Change Event