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

 

[Rick Rothstein]

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

Could you explain what you mean in more detail please (I do not see the logic behind the numbers you put in Column H in your table)?<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[lisburl]

The values of the cells in each row are vlookups that indicate whether a certain criteria was met. Everytime the criteria is not met (0) the count needs to reset. I only need to count the most recent number of consecutive occurrences of criteria being met (basically from column G backward). I have over a 1000 rows of data.

 

[Rick Rothstein]

The values of the cells in each row are vlookups that indicate whether a certain criteria was met. Everytime the criteria is not met (0) the count needs to reset. I only need to count the most recent number of consecutive occurrences of criteria being met (basically from column G backward). I have over a 1000 rows of data.

I kind of thought that, but the 1 in the fourth row seemed wrong given the 0 in the fifth row... shouldn't the fourth row be 0 also?

 

[lisburl]

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]

 

[Rick Rothstein]

I kind of thought that, but the 1 in the fourth row seemed wrong given the 0 in the fifth row... shouldn't the fourth row be 0 also?

Assuming what I said above is correct, try putting this formula in cell H1 and copy it down to the end of your data...

=IFERROR(IF(G1=0,0,LOOKUP(2,1/(B1:G1=1),COLUMN(B1:G1))-LOOKUP(2,1/(B1:G1=0),COLUMN(B1:G1))),6)

 

[lisburl]

Thank you for your assistance. Unfortunately, it's returning a 6 for every row that ends in 1 or a 0 for every row that ends in a 0

 

[immdav]

Lisburl,

If you can reverse your data so the total streak of 1,s is in column B and the values for G is in column C ect...
you could use this formula
=MATCH(0,C2:H2,0)-1


If you do this task often I think the answer is a User Define function, are you conformable with VBA?

 

[immdav]

I have a solution that takes two sheets
assuming the first row of nonheader data is row 2

place this in G2 on Sheet2 auto fill to B2 then auto fill the length of your data
=IF(SUM(Sheet1!$G2:G2)=COUNT(Sheet1!$G2:G2),1,0)

add =Sheet1!A2 to sheet2!A2 copy down
then sum columns b to G in column H
Not a good solution if you have to do this task often.
I could write up a UDF for you if you like.

 

[Rick Rothstein]

Thank you for your assistance. Unfortunately, it's returning a 6 for every row that ends in 1 or a 0 for every row that ends in a 0

Are you referring to the formula I posted? If so, you should know that it returns the same values in Column H for me that you posted.

Where is your data physically located at?

Also, are the numbers in your cells real numbers or are they text values that look like numbers (in other words, what are the cell formats for your numbers)?