Counting the number of times a value is repeated twice in a row

Counting the number of times a value is repeated twice in a row

I want to count how many times 0 occurs twice consecutively. See link at the bottom (blue text).

I've tried: =SUM(IF(FREQUENCY(IF(C7:BA7="0";ROW(C7:BA7));IF(C7:BA7="0";ROW(C7:BA7)))=2;1))

With Ctrl+Shift+Enter, but it is not working correctly.

This is an image of what im working with. I want to count how many times 0 occurs twice. Context for my problem is; I wanna be able to count the occurrences of how many times a product has not sold anything for two weeks in a row. In the picture 0 stands for zero sales that week, where blanks is registered sales. So if Week 1 and Week 2 has a 0, it should be counted as 1 episode of no sales for two weeks. The formula should then move on to find the next occurence of an two weeks with zero sales. if three 0 occur in row it shouldn't be counted twice just once. But if four 0 occur it should be counted twice. :) Thank you all in advance and merry Christmas.. it's my first post so sorry for the mess...

Answer by Tom Sharpe for Counting the number of times a value is repeated twice in a row

I would set up a helper row with a counter starting in C8 to increment during a run of zeroes and reset on a non-zero:-

=IF(C7=0,B8+1,0)  

Then a second helper row starting in C9 to pick off the last count in each run:-

=IF(D8=0,C8,0)  

And count how many of these are greater than 1:-

=COUNTIF(C9:J9,">1")  

B8 and K8 are assumed to be empty or zero.

Answer by ambrosen for Counting the number of times a value is repeated twice in a row

This works fine to look at columns A to H: {=SUM(IF(A1:H1=B1:I1,1,0))}. It's an array formula which compares A1 with B1, then B1 with C1 ... H1 with I1, and adds 1 to the sum if they're the same, or 0 otherwise.

Should work fine. Use Ctrl+Shift+Enter to make it work as an array formula, and don't type in the { & }, obviously.

Answer by Laesn for Counting the number of times a value is repeated twice in a row

[This is what i'm working wiht, see link for picture. My problem is that i wanna count the occurrences of how many times two 0 occur in a row. A run of three zeros should not be counted twice.

Context for the problem: is that i wan't to count how many times a product have gone two weeks without a single sale. And as you'll see in the picture no sales are registered with a 0 and sales are just blank. So if Week 1 and Week 2 have a 0, it should count for 1. This way i can tell our external sales force that they need to look into the presentation etc. of this product, because it hasen't sold anything for two tweeks. So anytime two 0 appear continously across the weeks, it should be counted as a single occurence, and then formula should look for the next occurence of two 0 (two weeks without sales). ]1

Answer by Tom Sharpe for Counting the number of times a value is repeated twice in a row

I will post this as another answer, for which much credit must go to @ambrosen.

So the formula to find each run of two or more zeroes followed by a blank and count the number of occurrences is:-

=SUM(ISNUMBER(B3:S3)*ISNUMBER(C3:T3)*ISBLANK(D3:U3))  

must be entered as an array formula using CtrlShiftEnter

So for the first two products in the image:-

Answer by Tom Sharpe for Counting the number of times a value is repeated twice in a row

Happy Christmas!

Call this as a function e.g.

=CountPairs(B3:S3)  

NB only works for row ranges in present version.

Function CountPairs(rng As Variant) As Integer  Dim v As Variant  Dim count, length, i As Integer  Dim found As Boolean    count = 0  found = False    v = rng  length = UBound(v, 2)    For i = 1 To length    ' Count if second of pair    If Not (IsEmpty(v(1, i))) Then      If found Then          count = count + 1          found = False      Else          found = True      End If  Else      found = False  End If    Next i    CountPairs = count    End Function  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72