Solved

# Count "x" number in a row

Posted on 2012-04-11
464 Views
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
0
Question by:spudmcc
• 12
• 9
• 7
• +3

LVL 43

Expert Comment

What is the answer for the above example?
0

LVL 45

Expert Comment

So I guess the answer is 6.
0

Author Comment

Yes, it would be six.
0

LVL 26

Expert Comment

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

Author Comment

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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
0

LVL 26

Expert Comment

Oh, I've assumed that this must be a formula. Is that correct? (Of course a macro would be pretty simple!)
0

LVL 26

Expert Comment

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

LVL 80

Expert Comment

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.

0

LVL 26

Expert Comment

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

LVL 85

Expert Comment

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

LVL 26

Assisted Solution

redmondb earned 250 total points
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.
0

LVL 85

Expert Comment

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. :)
0

LVL 26

Expert Comment

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

LVL 26

Expert Comment

Trivial point, Rory - can't "SumProduct" be replaced by "Sum"?
0

LVL 85

Expert Comment

For that syntax, yep.
0

Author Comment

Yes, the number of columns could vary but each cell will always contain a "1" or a "0".

Andy (Spudmcc)
0

LVL 26

Expert Comment

Andy,

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

Regards,
Brian.
0

LVL 85

Expert Comment

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?
0

Author Comment

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
0

LVL 85

Accepted Solution

Rory Archibald earned 250 total points
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)))))
0

Author Comment

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

LVL 85

Expert Comment

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

Author Comment

You are absolutely correct.  Could I please ask the moderator to split the points between rorya and redmondb.

Thanks Rory!

Andy
0

LVL 85

Expert Comment

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

LVL 26

Expert Comment

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

LVL 85

Expert Comment

0

Author Closing Comment

Thanks Experts for the help!

Andy
0

LVL 85

Expert Comment

you're welcome. :)
0

LVL 26

Expert Comment

Thanks all!
0

## Featured Post

### Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦