Count number of value changes in a column

[scoffman]

I have a spreadsheet in which each column contains cells with either a Y or a Z. I need a formula that looks at a range of cells and from top to bottom goes down the column and counts the number of times the value changes from Y to Z and Z to Y.

Not sure if this belongs in the countif or sumproduct arena, but any help would be appreciated. Thanks!

[Haseeb Avarakkan]

Hello, Try this;

=SUMPRODUCT(--(B3:B31<>B2:B30))

[Ron Coderre]

Using your posted workbook...
This regular formula returns the count of letter toggles for Col_B

Please Login or Register  to view this content.

Copy that formula across through D33

Using your posted data these are the results:
B33: 5
C33: 1
D33: 3

Is that something you can work with?

[scoffman]

Thank you!

[danpool]

Sorry about posting in an old thread, but this thread helped me solve my problem. I had to count the number of times that a valve opened during the course of a day. The time stamped data showed a 1.00 when the valve was opened and a 0.00 when the valve was closed. I didn't want to count the number of times that the valve closed, as the formula above would have done. It also would have counted intermediate values.

0:00:01	0.00
0:00:02	0.33
0:00:03	1.00
0:00:03	1.00
0:00:04	0.75
0:00:05	0.00

The previous formula would have counted this data above as 4, but really the valve only opened (went to 1.00) once. So I Ron's formula to what you see below.

Please Login or Register  to view this content.

This is a great forum. I've learned much from these pages.

[Sp00gles]

Hi ... I have a similar but different query.
I want a solution that will track the number of times a cell changes value, but I would like the output of this calculation or sum in the adjacent cell.
For example: Column B, I have a date and time field in format [dd/mm/yy_hh:mm]- This gets updated as an event changes, and now so often that the number of changes must be counted for a KPI.

thus I would like my table to display... Cell B1 = [dd/mm/yy_hh:mm]. Cell C1= Number of changes to B1.

Can anybody help?

[Glenn Kennedy]

Sp00gles. Please do not post your query in a long-dead thread. It's against the rules. Please start your OWN thread.

Thread closed.