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

youra6

Board Regular
Joined
Mar 31, 2014
Messages
95
Greetings again,

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

day1day2day3day4day5# of changes
hash1100103
hash2000000
hash3111101

<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:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
Assuming your table starts in A1, try:

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

youra6

Board Regular
Joined
Mar 31, 2014
Messages
95
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
Joined
Sep 18, 2013
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

 

youra6

Board Regular
Joined
Mar 31, 2014
Messages
95

ADVERTISEMENT

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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Sep 18, 2013
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top