Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

```
=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

=SUMPRODUCT((FREQUENCY(IF(

to

=SUMPRODUCT((FREQUENCY(IF(

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialAndy (Spudmcc)

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

Thanks Rory!

Andy

I appreciate the effort, but really don't worry about it! Rory's excellent solution did the job - and I learned a lot from it. Win:win!

Regards,

Brian.

Microsoft Applications

From novice to tech pro — start learning today.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.