# Count "x" number in a row

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
###### Who is Participating?

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

EngineerCommented:
What is the answer for the above example?
Older than dirtCommented:
So I guess the answer is 6.
Author Commented:
Yes, it would be six.
Commented:
Hi, 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, four zeros in a row only count as three (because the the three zeros have been "used up" before the fourth one comes along). So for your example, it gives six, but it also gives six for the following...

1      1      0      0       1      0      0       0     0    1     0    0    0    1

Is this correct?

Regards,
Brian.
Author Commented:
The amount of items in each row could vary.  It isn't always in columns A-J.
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.
Commented:
spudmcc,

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.
Commented:
spudmcc,

Pending answers to the above, please see the attached. A few points...
(1) It handles the first 14 or fewer columns. It can be easily changed to handle more columns.
(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
Commented:
Oh, I've assumed that this must be a formula. Is that correct? (Of course a macro would be pretty simple!)
Commented:
... and here is that macro. It ignores column O and outputs to column Q...
``````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
``````
Mechanical EngineerCommented:
Brian,
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.

Commented:

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.
Commented:
=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)))))
array-entered should work. Can be extended for different criteria than 3 quite easily. Barry can probably do it in 10 characters or fewer...
Commented:
Rory,

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.
Commented:
Ah, my understanding was that there were always values in the cells, but the number of columns might alter, in which case simply changing the ranges is all that's necessary. For blank cells, yes, we need an adjustment like that. :)
Commented:
Rory,

"my understanding was that there were always values in the cells"
Yes, you could be right. The exact format is unclear and we're waiting for a response from the OP.

Regards,
Brian.
Commented:
Trivial point, Rory - can't "SumProduct" be replaced by "Sum"?
Commented:
For that syntax, yep.
Author Commented:
Yes, the number of columns could vary but each cell will always contain a "1" or a "0".

Andy (Spudmcc)
Commented:
Andy,

Thanks. So how should the formula recognise the number of columns?

Regards,
Brian.
Commented:
Do you mean a different number of columns in use for each row, or can you simply adjust the ranges in the formulas provided earlier to match your data?
Author Commented:
Each row will have the same number of columns but workbook to workbook the number of columns may change.  Todays workbook may have 20 columns but tomorrows might have 25.  We only have to be concerned with 1 day at a time and never need to merge books but the solution need to be flexible enough that it can be used with different column numbers.

I hope that makes some sort of sense.

A
Commented:
Well, I'm not sure what you mean by flexible - all you have to do with the formulas is change the ranges to point at the columns you want to check. So if you need to check cols B:X change the formula from

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

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

Experts Exchange Solution brought to you by

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

Author Commented:
This works great!  I assumed I would have to change the range and that isn't any problem at all.  Thanks for all of your hard work, time and patience.

Andy (Spudmcc)
Commented:
I think a points split would be more appropriate since Brian gave you several options for solutions?
Author Commented:
You are absolutely correct.  Could I please ask the moderator to split the points between rorya and redmondb.

Thanks Rory!

Andy
Commented:
I've requested mod attention to get this reopened and you can then re-allocate as you wish. :)
Commented:
Folks,

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.
Commented:
Author Commented:
Thanks Experts for the help!

Andy
Commented:
you're welcome. :)
Commented:
Thanks all!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.