Be seen. Boost your questionâ€™s priority for more expert views and faster solutions

Hi Experts

I am looking for a solution for something that seems very simple on the surface but I am not sure if it really is in reality.

Here's the challenge:

A B C D E F G H I J K L M N

Row: 1 1 0 0 1 0 0 0 1 1 0 0 0 1

I am looking to ONLY count the zero's in the above example when there is 3 in a row, ignoring all others values as well as any zero that isn't 3 in a row.

Any help would be greatly appreciated as always.

spudmcc

I am looking for a solution for something that seems very simple on the surface but I am not sure if it really is in reality.

Here's the challenge:

A B C D E F G H I J K L M N

Row: 1 1 0 0 1 0 0 0 1 1 0 0 0 1

I am looking to ONLY count the zero's in the above example when there is 3 in a row, ignoring all others values as well as any zero that isn't 3 in a row.

Any help would be greatly appreciated as always.

spudmcc

```
=14-LEN(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1),"000","###"),"#",""))
```

The above assumes that, for example, 1 1 0 0 1 0 0 0

Is this correct?

Regards,

Brian.

Also, I should clarify that it should count anything >=3. So if we have

A B C D E F G H I J K L M

Row1 1 0 0 0 0 1 0 0 0 1 0 0 0

Row 2 1 1 0 0 1 1 1 1 1 0 0 0 0

Row 1 would equal 10

Row 2 would equal 4

Sorry for not being clear the first time.

Thanks, but we need some more, please!

(1) What is the maximum no. of columns?.

(2) Can we shift the columns to the right, so that the formula is in column A?

(3) When a column isn't being used, what will be in it? (It would be ideal if you could give us a sample file!)

Thanks,

Brian.

Pending answers to the above, please see the attached. A few points...

(1) It handles the first 14

(2) The cells in column O must have a "1" in them. (Sorry, I'll try to get rid of this.)

(3) The formulas in column P must be array-entered (CTRL-SHIFT-ENTER). Let me know if you need more on this.

(4) I manually checked the results, as best I could. I'm hoping you have some test data as a definitive test!

The formula in P1 is

```
{=SUM(IF((A1:L1&""="0")*(B1:M1&""="0")*(C1:N1&""="0"),3-IF(D1:O1&""="0",2,0)))}
```

Regards,Brian.

Three-in-a-Row.xls

```
Option Explicit
Sub Three_in_a_Row()
Dim xLast_Row As Long
Dim i As Long
Dim j As Long
Dim xZeros As Long
Dim xCount As Long
Sheets("Sheet1").Activate
xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
For i = 1 To xLast_Row
xCount = 0
xZeros = 0
For j = 1 To 14
If Cells(i, j) = "0" Then
xZeros = xZeros + 1
If xZeros = 3 Then
xCount = xCount + 3
ElseIf xZeros > 3 Then
xCount = xCount + 1
End If
Else
xZeros = 0
End If
Next
Cells(i, 17) = xCount
Next
End Sub
```

You did better than you thought. I'm not seeing any need for column O to have a 1, having used Paste Special...Values to compare results with either ones or blanks in column O.

Brad

Oops, I should have said "non-zero" rather than "1". (The "1" was a hang-over from an earlier version.)

The problem is that I need an array of D1:N1's values - but it needs to have 12 entries not 11, hence column O. Any idea how I could do this without the extra column?

Regards,

Brian.

array-entered should work. Can be extended for different criteria than 3 quite easily. Barry can probably do it in 10 characters or fewer...

Nice one! Small change (to handle less than 14 entries as requested by OP - see my last two examples)...

```
{=SUMPRODUCT((FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))))}
```

Regards,Brian.

"

Yes, you could be right. The exact format is unclear and we're waiting for a response from the OP.

Regards,

Brian.

Andy (Spudmcc)

I hope that makes some sort of sense.

A

Andy (Spudmcc)

I think a points split would be more appropriate since Brian gave you several options for solutions?

Thanks Rory!

Andy

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=SUMPRODUCT((FREQUENCY(IF(

to

=SUMPRODUCT((FREQUENCY(IF(

B1:X1=0, COLUMN(B1:X1)), IF(B1:X1<>0, COLUMN(B1:X1)))>=3)*(FREQUENCY(IF(B1:X1=0, COLUMN(B1:X1)), IF(B1:X1<>0, COLUMN(B1:X1)))))