Counting Cell value change in a column using macros

I am working on making a counter for a column.
The values in that column are either 0(off) or 1(on).

I want to know the count of how many times the switch goees from off to on(0 to1).

I have tried countf, sumproduct but none have worked out.

I then found a manual solution & used =IF(Cell2=0,IF(Cell1=1,1,0),0) in a New column next to it.

Then did a sum of the new column.

Can someone help work out a macro on this?

8EDIT* I also tried recording a macro but it wasn't working properly snce I could not get the same last row used in different files.

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Using a single cell formula without any helper column and all

Formula:

Please Login or Register  to view this content.

What is your exact excel version? Is that values are always stay in Column-A & Column-B? Can we determine the last row based on the Column-B Data?

Thanks for the confirmation at the same time please update your profile.

You can refer the suggested formula to whole columns too...

Please confirm the next column address whether it is AP?

Try this...

Please Login or Register  to view this content.

Glad it helps you and thanks for the feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.