# Count number of times changes occur in a row (0 to 1, 1 to 0)

#### youra6

##### Board Regular
Greetings again,

Lets say I have some hashes with syncable binary flags: 1 being syncable, 0 being unsyncable:

 day1 day2 day3 day4 day5 # of changes hash1 1 0 0 1 0 3 hash2 0 0 0 0 0 0 hash3 1 1 1 1 0 1

<tbody>
</tbody>

The last column will count the number of times a hash went from (1 to 0) or (0 to 1). In hash1, the change occurred 3 times, in hash3, the change occurred only 1 time.

I've seen formulas that can solve this if the values were transposed into columns. But I've not been able to figure it out when the 1, 0 are in row form.

Any help is appreciated!

Last edited:

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### StephenCrump

##### MrExcel MVP
Assuming your table starts in A1, try:

G2: =SUMPRODUCT(--(B2:E2+C2:F2=1))

#### youra6

##### Board Regular
Assuming your table starts in A1, try:

G2: =SUMPRODUCT(--(B2:E2+C2:F2=1))

That does the trick, thank you! Since its using the SUMPRODUCT Function, I'm guessing there isn't a work around if a cell contains a string value like "NULL"?

Last edited:

#### StephenCrump

##### MrExcel MVP
Probably better to use the more robust:

G2: =SUMPRODUCT(--(B2:E2<>C2:F2))

That does the trick, thank you! Since its using the SUMPRODUCT Function, I'm guessing there isn't a work around if a cell contains a string value like "NULL"?

Does this mean you're looking for results like this:

ABCDEFG
1day1day2day3day4day5# of changes
2hash11NULL0NULL12
3hash2NULL010NULL2

</tbody>

#### youra6

##### Board Regular

Probably better to use the more robust:

G2: =SUMPRODUCT(--(B2:E2<>C2:F2))

Does this mean you're looking for results like this:

ABCDEFG
1day1day2day3day4day5# of changes
2hash11NULL0NULL12
3hash2NULL010NULL2

<tbody>
</tbody>

Yes, exactly like you just described! Your suggestion above fixed the NULL issue, I'm going to go QC it to see it still works.

Edit: Ah, because of the change to your formula, it counts NULL as a change as well. Thats not a huge issue but if you can make it work the way your example did, that would be even more fantastic.

Last edited:

##### MrExcel MVP
Yes, exactly like you just described! Your suggestion above fixed the NULL issue, I'm going to go QC it to see it still works.

Edit: Ah, because of the change to your formula, it counts NULL as a change as well. Thats not a huge issue but if you can make it work the way your example did, that would be even more fantastic.

NULL stands probably for an empty (unused) cell, thus as such it does not exist in the data.

#### StephenCrump

##### MrExcel MVP
If you're stuck with "NULL" or similar non-counted values, then a formula approach becomes more difficult, and I'd tend to switch to VBA.

Here's my (quick and dirty!) code:

G2: =CountChanges(B2:F2,"null")
G5: =CountChanges(B5:F5,"")

ABCDEFG
1day1day2day3day4day5# of changes
2hash1null1null1null0
3hash2null01null02
4
5hash1110
6hash20102

<tbody>
</tbody>
Code:
``````Function CountChanges(rngRow As Range, Optional sExclude As String = "NULL") As Long

Dim vCurrent As Variant
Dim lCount As Long, i As Long

lCount = -1
vCurrent = -1
For i = 1 To rngRow.Columns.Count
If rngRow(i).Value <> vCurrent And UCase(rngRow(i).Value) <> UCase(sExclude) Then
lCount = lCount + 1
vCurrent = rngRow(i).Value
End If
Next i

CountChanges = Application.Max(lCount, 0)

End Function``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,160
Messages
5,835,733
Members
430,383
Latest member
Kastore

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back