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

Question

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

If **0** occurs three times consecutively in a row, how should it be counted?? — Gary's Student, Dec 22 '15 at 14:40
just once based on the example... A, B,C all 0 and question said in the above just 1... — xQbert, Dec 22 '15 at 14:56
Yeah okay i get that. The 0 represents zero sales that day. where the blanks (-) presents sales. Its raw data from external database. Where 0 represents missing sales either due to disitrbution problems or just no sales..So when retrieveing the data i get 0's when no sales has been registered and blanks if their has been sales...And i want to count the number of times "0" (zero sales) has been registered twice in a row. — Laesn, Dec 22 '15 at 15:19
Near impossible to understand your requirement with just the single example you've posted. — XOR LX, Dec 22 '15 at 15:48
@XORLX I've edited the comment and if you would be so kind to take a look at it i would appreciate it. Thank you. — Laesn, Dec 23 '15 at 08:02
@xQbert I've edited the comment and if you would be so kind to take a look at it i would appreciate it. Thank you. — Laesn, Dec 23 '15 at 08:02
Thanks, but actually it was more one than example that I was hoping for, together with expected result in each case. Also, your attempt at a table is quite poor, I'm afraid. Why only columns A, B and C? What if there are zeroes in columns F, G and J, for example? — XOR LX, Dec 23 '15 at 08:30
@XORLX - I agree the first example is very poor. But if you click the link in the bottom (blue text) the example is more justifiable. The columns would spread from A to BC. But take a look at the link/picture you'll get a better understanding of my problem and the solution im searching for. Thank you. — Laesn, Dec 23 '15 at 09:15
True, but such pictures are non-pasteable into an Excel sheet, and so unless I were to manually recreate that example cell-by-cell I would not have any data on which to test potential solutions. And unfortunately I'm not willing to do that. Hopefully others will, though. — XOR LX, Dec 23 '15 at 09:19

Tom Sharpe · Answer 1 · 2015-12-27T17:41:07.733

0

With the additional information that all distinct, non-overlapping pairs should be counted and non-zero cells may be empty, I would set up a helper row with a formula starting in B4 to count down from zero during each run of zeroes and work out the number of pairs at the end of each run of zeroes:-

=IF(AND(B3=0,LEN(B3)=1),IF(A4<=0,A4-1,-1),IF(A4<0,INT(ABS(A4)/2),0))

Then sum the number of pairs in the row (ignoring any negative numbers) with the following formula in U4:-

=SUMIF(B4:T4,">0")

edited Dec 27 '15 at 17:41

answered Dec 22 '15 at 16:05

Tom Sharpe

30,727
5
24
37

I really appreciate your help so far. But i would really like not to have to many helper cells, and i can't seem to fix my problem with your formula. But if you would be so kind to look at my own "answer" i just submitted and see if you could me i would appreciate it! I've linked a picture and the context of my problem. Thanks in advance. – Laesn Dec 23 '15 at 07:53

score 0 · Answer 2 · answered Dec 22 '15 at 16:13

0

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.

answered Dec 22 '15 at 16:13

ambrosen

101
2
5

I like this answer (no helper rows unlike mine), but might need to add an extra condition to check that only pairs of zeroes are counted? Also still not entirely clear whether a run of 3 zeroes should count once or twice. – Tom Sharpe Dec 22 '15 at 16:39
@ambrosen I really appreciate your help so far. Though i can't make it work with the formula you wrote. Please see my comment above, where i've linked a picture and a text that gives context for my problem. Thanks in advance. Much appreciated! – Laesn Dec 23 '15 at 07:49
If your actual data is zeroes and blanks, then I would suggest a modification to ambrosen's excellent suggestion =SUM(IF(ISNUMBER(C7:J7)*ISNUMBER(D7:K7)*ISBLANK(E7:L7),1,0)) for my test data which runs from C7 to J7. – Tom Sharpe Dec 23 '15 at 09:31
Or =SUM(ISNUMBER(C7:J7)*ISNUMBER(D7:K7)*ISBLANK(E7:L7)) don't need the IF. – Tom Sharpe Dec 23 '15 at 09:33

score 0 · Answer 3 · answered Dec 23 '15 at 07:43

[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

score 0 · Answer 4 · edited May 08 '18 at 21:49

0

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

With the additional information that all distinct, non-overlapping pairs should be counted and non-zero cells may be empty, a pure array-formula solution is extremely difficult to achieve, but a compromise solution would be to use an existing array-concatenation function like the one here to copy the range into a string, then substitute each pair of zeroes with a single character:-

=LEN(StringConcat("",IF(B3:S3="","/",B3:S3)))
-LEN(SUBSTITUTE(StringConcat("",IF(B3:S3="","/",B3:S3)),"00","0"))

edited May 08 '18 at 21:49

Community

1
1

answered Dec 23 '15 at 10:17

Tom Sharpe

30,727
5
24
37

Just realised this still isn't what you want because the run of six zeroes should count as 3 not 1. This is starting to get very tricky to do with an array formula. – Tom Sharpe Dec 23 '15 at 11:34
Again thank you for your time and consideration. But yes it's driving me nutsaswell! its really hard to figure out how to do it without dividing up the table in two weeks at a time and then just use countif. But that would take a long time and be a mess as well – Laesn Dec 23 '15 at 12:20
I think you need to use Frequency to tell excel that you're looking for a speficic sequence that might repeat itself acros various columns. I just cant figure out how to do it.. – Laesn Dec 23 '15 at 12:30
Someone else might be able to solve it with an array formula but IMHO the underlying problem is that you need to carry the current state forward for each comparison which leads to either a VBA solution or back to helper rows... – Tom Sharpe Dec 23 '15 at 12:35
any solution would do. I dont have experience with VBA, but i'll figure out how to add the code/script in excel. – Laesn Dec 23 '15 at 12:43

score 0 · Accepted Answer · edited Dec 25 '22 at 20:15

0

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

edited Dec 25 '22 at 20:15

VLAZ

26,331
9
49
67

answered Dec 23 '15 at 14:14

Tom Sharpe

30,727
5
24
37

It Works!! Many thanks four your time and work on this! Very much appreciated :) Merry Christmas and Happy new year! – Laesn Dec 23 '15 at 14:55
Is it also possible to count pairs of 0, but instead of blanks theres various different numbers..? Have to different databases from to different wholesalers all deliver different spreadsheets.. Thanks in advance. – Laesn Dec 23 '15 at 15:37
Yes, if you change the first line of the IF statement to If v(1, i) = "0" Then it should distinguish between 0's and anything else. – Tom Sharpe Dec 23 '15 at 17:36

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

5 Answers5