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

L

lisburl

New Member
Joined
Apr 7, 2018
Messages
8
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>[TABLE="width: 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>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="bgcolor: #fff2cc"]H TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #fff2cc"]0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]2[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #fff2cc"]3[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #fff2cc"]0[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help.
 
Sort by date Sort by votes
lisburl said:
Oops! Yes - you are correct. This is why I need a formula!! When I try to do this manually I make mistakes :)

[TABLE="class: cms_table, width: 0"]
<tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="bgcolor: #FFF2CC"]H TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #FFF2CC"]0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #FFF2CC"]0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]2[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]1[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]3[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: #FFF2CC"]0[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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

​
[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Data01[/TD]
[TD]Data02[/TD]
[TD]Data03[/TD]
[TD]Data04[/TD]
[TD]Data05[/TD]
[TD]Data06[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


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

Markmzz
 
Last edited:
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
markmzz said:
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),))
​
Click to expand...
​
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.
 
Upvote 0
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
 
Upvote 0
immdav said:
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
Click to expand...
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...
Code: 
[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]
 
Upvote 0
immdav said:
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?
Click to expand...

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
 
Upvote 0
Markmzz,

Thanks for the explanation.

Rick,

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

David
 
Upvote 0
immdav said:
Rick,

cleaver use of the index and split function. Anything to avoid a loop....
Click to expand...
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. :diablo:
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.
Replies
2
Views
162
VinnyDoesntKnowExcelCode
V
E
  • Solved
Identify product that have different values with a spilled formula
Replies
3
Views
74
ED38
E
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen
C
  • Question Question
Formula not working to show total sales per day, #VALUE! Can't find the issue (Ignore Conditionals) I3 works fine, J3&J4 are what I have tried.
2
Replies
15
Views
594
Asbestos_Jen
Asbestos_Jen
gtd526
  • Question Question
convert cell value to formula
Replies
8
Views
360
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top