How to count change in column

**zit1343** · 06-28-2011, 01:01 PM

Hi
I am generating lots of read in one column
around over 1000, it is done using some addon
that is not the problem, the problem is how to count the change in the column

the column will only contain OPEN OR CLOSE
it could be either
CLOSE
CLOSE
CLOSE
OPEN
CLOSE
CLOSE
OPEN
OPEN
OPEN
CLOSE

so what I need to do it count the number of change of status
for this above example it would be 4 times it changed in entier so I need to findout the way to count the number of changes please help
Thank you
zit

**NBVC** · 06-28-2011, 01:14 PM

If your range is in A1:A10, then try:

=SUMPRODUCT(--(A1:A9="Close"),--(A2:A10="Open"))+SUMPRODUCT(--(A1:A9="Open"),--(A2:A10="Close"))

**zit1343** · 06-30-2011, 10:41 AM

hey what if I do not know the limit is going up to,
it is different everytime

**NBVC** · 06-30-2011, 10:45 AM

In a separate column, say in B1, enter formula:

=MATCH(REPT("z",255),A:A) this will get last row in column A that is occupied.

Then change formula to:

=SUMPRODUCT(--(A1:INDEX(A:A,$B$1-1)="Close"),--(A2:INDEX(A:A,$B$1)="Open"))+SUMPRODUCT(--(A1:INDEX(A:A,$B$1-1)="Open"),--(A2:INDEX(A:A,$B$1)="Close"))

**zit1343** · 06-30-2011, 11:02 AM

man you are awesome, I have only one problem
in A1 I have the Title.
is there a way to fix it ?
=MATCH(REPT("z",255),A:A)

**NBVC** · 06-30-2011, 11:03 AM

Just adjust the formula accordingly:

=SUMPRODUCT(--(A2:INDEX(A:A,$B$1-1)="Close"),--(A3:INDEX(A:A,$B$1)="Open"))+SUMPRODUCT(--(A2:INDEX(A:A,$B$1-1)="Open"),--(A3:INDEX(A:A,$B$1)="Close"))

**zit1343** · 06-30-2011, 12:12 PM

nevermind I got it
thanx alot. let see where else do I get stuck
I fixed it by removing by $ sign

**NBVC** · 06-30-2011, 12:17 PM

Perhaps you just need to move the $ from before the B's?

=SUMPRODUCT(--(A2:INDEX(A:A,B$1-1)="Close"),--(A3:INDEX(A:A,B$1)="Open"))+SUMPRODUCT(--(A2:INDEX(A:A,B$1-1)="Open"),--(A3:INDEX(A:A,B$1)="Close"))

If still not it, please post your workbook.

**zit1343** · 06-30-2011, 12:37 PM

I removed "$" from $B$1 to B1-1
and it seems to be working any reason we need $ in front of 1

**NBVC** · 06-30-2011, 12:41 PM

The $ "freezes" the column B and/or Row 1... so that when you copy across or down those don't change....

You can remove the $ before the 1 if you want, but if you copy the formula downwards, then beware that it will then reference B2, B3, B4, etc....

here is more on Absolute/Relative Referencing