Formula to count how many adjacent cells have value that precede specified cell

[lisburl]

Good afternoon!

I need help with a formula that will give me the total the number of cells in a row that contain a value that are adjacent to a target cell (H).

For example: Column H would total the following
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

B	C	D	E	F	G	H TOTAL
0	0	0	1	0	1	1
1	1	0	1	0	1	1
0	1	0	1	0	1	1
0	0	1	1	0	0	1
0	1	0	1	0	0	0
0	1	0	0	1	1	2
0	0	0	1	0	1	1
0	1	0	1	0	1	1
0	1	0	1	1	1	3
0	0	1	1	0	0	0

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

Thank you for any help.

 

[markmzz]

Oops! Yes - you are correct. This is why I need a formula!! When I try to do this manually I make mistakes

B	C	D	E	F	G	H TOTAL
0	0	0	1	0	1	1
1	1	0	1	0	1	1
0	1	0	1	0	1	1
0	0	1	1	0	0	0
0	1	0	1	0	0	0
0	1	0	0	1	1	2
0	0	0	1	0	1	1
0	1	0	1	0	1	1
0	1	0	1	1	1	3
0	0	1	1	0	0	0

<tbody>
</tbody>

Maybe the formula below in H2 and copy down can helps:

=COLUMNS(B2:G2)-MATCH(2,INDEX(1/(B2:G2=0),),1)

Or

​

=COLUMNS(B2:G2)-MATCH(2,INDEX(1/(B2:G2=0),))

​

	A	B	C	D	E	F	G	H	I
1		Data01	Data02	Data03	Data04	Data05	Data06	Result
2		0	0	0	1	0	1	1
3		1	1	0	1	0	1	1
4		0	1	0	1	0	1	1
5		0	0	1	1	0	0	0
6		0	1	0	1	0	0	0
7		0	1	0	0	1	1	2
8		0	0	0	1	0	1	1
9		0	1	0	1	0	1	1
10		0	1	0	1	1	1	3
11		0	0	1	1	0	0	0
12
***	**	*******	*******	*******	*******	*******	*******	*******	**

<tbody>
</tbody>

By the way, Rick's formula works for me.

Markmzz

 

[Rick Rothstein]

Maybe the formula below in H2 and copy down can helps:

=COLUMNS(B2:G2)-MATCH(2,INDEX(1/(B2:G2=0),),1)

Or

=COLUMNS(B2:G2)-MATCH(2,INDEX(1/(B2:G2=0),))
​

​

You will need to put an IFERROR trap on your formulas like I did on mine in order to handle the case when all of the cells have 1's in them.

 

[markmzz]

You will need to put an IFERROR trap on your formulas like I did on mine in order to handle the case when all of the cells have 1's in them.

Thanks Rick.

=COLUMNS(B2:G2)-IFERROR(MATCH(2,INDEX(1/(B2:G2=0),)),0)

Markmzz

 

[immdav]

Ricks formula works for me


Mark and Rick your formulas are playing with my head a bit

I understand how INDEX(1/(B2:G2=0),) is evaluating to {1,#Div/0,1,#Div/0,#Div/0,#Div/0} (or some varation of)
Is the match(2,{1,#Div/0,1,#Div/0,#Div/0,#Div/0}) part looking for 2 but can't find it so it returns the position
of the last valid number it located?

I might be cheating but here is what I would do if it crossed my desk

Public Function NearestRunones(ByVal rngRun As Range)
Dim strRange As String
Dim Cell As Range
For Each Cell In rngRun
strRange = strRange & Cell.Value2
Next Cell
strRange = StrReverse(strRange)
If InStr(strRange, 0) > 0 Then
NearestRunones = InStr(strRange, 0) - 1
Else
NearestRunones = 0
End If

End Function

 

[lisburl]

Thanks Rick.

=COLUMNS(B2:G2)-IFERROR(MATCH(2,INDEX(1/(B2:G2=0),)),0)

Markmzz

This formula works perfectly!!! Thank you VERY MUCH Rick and Mark.

 

[Rick Rothstein]

Ricks formula works for me


Mark and Rick your formulas are playing with my head a bit

I understand how INDEX(1/(B2:G2=0),) is evaluating to {1,#Div/0,1,#Div/0,#Div/0,#Div/0} (or some varation of)
Is the match(2,{1,#Div/0,1,#Div/0,#Div/0,#Div/0}) part looking for 2 but can't find it so it returns the position
of the last valid number it located?

I might be cheating but here is what I would do if it crossed my desk

Public Function NearestRunones(ByVal rngRun As Range)
Dim strRange As String
Dim Cell As Range
For Each Cell In rngRun
strRange = strRange & Cell.Value2
Next Cell
strRange = StrReverse(strRange)
If InStr(strRange, 0) > 0 Then
NearestRunones = InStr(strRange, 0) - 1
Else
NearestRunones = 0
End If
End Function

Your function incorrectly reports 0 if all the cells are filled with 1's.

If we assume the range is alway a single row horizontal range (as in the OP's case), your function can be rewritten as a one-liner...

[table="width: 500"]
[tr]
	[td]Function NearestRunones(ByVal rngRun As Range) As Long
  NearestRunones = Len(Split(StrReverse(Join(Application.Index(rngRun.Value, 1, 0), "")), 0)(0))
End Function[/td]
[/tr]
[/table]

 

[markmzz]

Mark and Rick your formulas are playing with my head a bit

I understand how INDEX(1/(B2:G2=0),) is evaluating to {1,#Div/0,1,#Div/0,#Div/0,#Div/0} (or some varation of)
Is the match(2,{1,#Div/0,1,#Div/0,#Div/0,#Div/0}) part looking for 2 but can't find it so it returns the position
of the last valid number it located?

Hi Immdav!

Yes, you're right. The match(2,{1,#Div/0,1,#Div/0,#Div/0,#Div/0}) part is looking by a largest value that is less than or equal to 2 but it can't find it.
So it returns the position of the last value (errors are ignoreted) that is less than 2 (the last 1).

I hope that this helps.

Markmzz

 

[immdav]

Markmzz,

Thanks for the explanation.

Rick,

cleaver use of the index and split function. Anything to avoid a loop....

David

 

[Rick Rothstein]

Rick,

cleaver use of the index and split function. Anything to avoid a loop....

It was more my penchant for compact code and one-liners (and it is so much better when the two combine) than it was to avoid a loop.

 

[markmzz]

Markmzz,

Thanks for the explanation.

David

You are welcome.

Markmzz