How to count number of times a value in a chart switches column?

[stitch24]

I am not very experienced with excel, so I would appreciate any advice about how to proceed with a task I am trying to complete. I have attached a sample of the data I am working with - essentially it is a record of an animal's location and body position every minute for a period of several hours. The data was collected using a 1-0 sampling method, so a 1 in a cell indicates she is in that location or body position and a 0 indicates that she is not.

I am trying to quantify the activity level of the animal by counting every time she switches location or body position. However, if she switches location and body position at the same time, I want this to count as 1, not 2.

I am at a loss about how to do this other than counting the number of times each column changes from 1 to 0 or 0 to 1 and manually comparing every column to eliminate overlapping counts - any advice about how to proceed would be greatly appreciated.

[CK76]

I'd add 2 helper columns to be used as condition for each set. Then another column for tallying up if each period met the condition or not.

However, how do you intend on handling "NA" for each set? Does NA mean that observation wasn't made?

Do you count as switching column if it changed from Lying to NA or vice versa?

[stitch24]

Thank you for your help! The NA means that the animal was not visible for an observation to be made - that is a good point you bring up, I should delete the NA column.

[CK76]

Ok then. For now I excluded NA columns.

Here's brief set up:
1. AE3: =IFERROR(MATCH(1,$F3:$V3,0),AE2)
Copy down.

2. AF3: =IFERROR(MATCH(1,$Y3:$AB3,0),AF2)
Copy down.

IFERROR is used to carry over, previous period's observation if all columns return 0 (i.e. NA is 1).

Then in AG3:
=IF(ROWS($A$1:$A3)=3,0,--((($AE2<>$AE3)+($AF2<>$AF3))>0))
Copy down.

Basically this checks if either AE/AF changed from previous row. If yes, return 1, if no, return 0. First if argument is just there to check for first observation (i.e. not a change so 0).

See attached.

[stitch24]

Thank you very much for your help!