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!
Bookmarks