count number of times cell value changes by formula

[vijayamani1987]

Hi people,

I come across some similar posts but not able to modify it to my purpose.

In column C & F has if function, D&E has sum function. The formula in C:C is =IF(AND(M4>=3,N4>=2,O4="wait"),"wait",IF(AND(M4<=-3,N4<=-2,O4="close"),"close","-")). These values in D,E and F keeps changing for every 15 minutes and so C column. I want the number of times "wait" or "close" arrived in C column to be recorded in A & B column.


[TABLE="width: 785"]
<colgroup><col span="3"><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Wait[/TD]
[TD]Close[/TD]
[TD](if function)[/TD]
[TD](sum function)[/TD]
[TD](sum function)[/TD]
[TD](if function)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]wait[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]wait[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]close[/TD]
[TD]-5[/TD]
[TD]-3[/TD]
[TD]close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]-6[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]-3[/TD]
[TD]-7[/TD]
[TD]wait[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]close[/TD]
[TD]-6[/TD]
[TD]-4[/TD]
[TD]close[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 713"]
<colgroup><col span="4"><col span="3"><col></colgroup><tbody>[TR]
[TD]Any help appreciated.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 713"]
<colgroup><col span="4"><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 713"]
<colgroup><col span="4"><col span="3"><col></colgroup><tbody></tbody>[/TABLE]

 

[RasGhul]

Copy across from A to B

=COUNTIF($C:$C,A1)