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.

 

[lisburl]

I figured out why this formula did not work for me at first. I had added an additional so my data had shifted from C to H.

It works now that I updated it to reflect the correct columns. I have run into a challenge though because occasionally the vlookups are returning empty cells. This does not affect the outcome if the last column is a 0, but if it's a 1 it is adding the blank cells as 1s. Is there any way around this?

BTW - your formula has saved me sooooo much time!! Thank you!!!

 

[markmzz]

I figured out why this formula did not work for me at first. I had added an additional so my data had shifted from C to H.

It works now that I updated it to reflect the correct columns. I have run into a challenge though because occasionally the vlookups are returning empty cells. This does not affect the outcome if the last column is a 0, but if it's a 1 it is adding the blank cells as 1s. Is there any way around this?

BTW - your formula has saved me sooooo much time!! Thank you!!!

Hi!

I'm happy that my suggestion help you.

Try the new version below:

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

Markmzz

 

[lisburl]

Works perfectly!!

 

[markmzz]

Works perfectly!!

I'm glad to help and thanks for the feedback.

Markmzz

 

[lisburl]

New issue popped up - We are collecting data for more than six months now, and I'd like to extend the formula. So instead of B:G I need it to go from B:M. The issue is that data is uploaded at different times and the blank cells that precede the formula cell make the formula result in a 0. (If the blank cells precede cells that have a 1 or 0 the sum is not affected).

For instance:Everything works if the formula ends at G: =COLUMNS(B2:G2)-IFERROR(MATCH(2,INDEX(1/((B2:G2=0)+(B2:G2="")),)),0)
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

B

C

D

E

F

G

H

I

J

K

L

M

N

1

0

1

1

2

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

It doesn't work if the formulat ends at M: =COLUMNS(B2:M2)-IFERROR(MATCH(2,INDEX(1/((B2:M2=0)+(B2:M2="")),)),0)<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

1

1

0

1

1

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

Any suggestions?
Thank you!!