Counting Consecutive Values left to right?

**metaxzen** · 07-18-2014, 02:47 PM

Greetings! I have been trying to adapt a couple of the posts I've seen on this topic but they seem to refer to counting in rows and I'm having trouble translating that to columns.

I have a simple situation. I have 1s or 0s in columns from left to right. I only want to count the first occurrence of consecutive "1"s and only if the series starts with "1" in the left most column. (I am trying to calculate consecutive years of giving and the columns are FY14, FY13, FY12, etc. Only people who gave in FY14 would count.)

Example:

Row 2: 11101010
Row 3: 11001011
Row 4: 00100110

I would like Row 2 to count "3", Row 3 to count "2" and Row 4 to count "0".

Thank you!
- Anthony

**ConneXionLost** · 07-18-2014, 02:55 PM

If your data is complete (i.e., no blanks), then try:

Please Login or Register  to view this content.

Cheers,

**Tony Valko** · 07-18-2014, 02:58 PM

Try this...

Data Range

	A	B	C	D	E	F	G	H	I	J
1	-----	-----	-----	-----	-----	-----	-----	-----	-----	-----
2	1	1	1	0	1	0	1	0		3
3	1	1	0	0	1	0	1	1		2
4	0	0	1	0	0	1	1	0		0
5	1	1	1	1	1	1	1	1		8
6	0	0	0	0	0	0	0	0		0

This formula entered in J2 and copied down:

=IF(A2=1,IF(COUNTIF(A2:H2,0),MATCH(0,A2:H2,0)-1,SUM(A2:H2)),0)

**okido** · 12-11-2016, 12:55 PM

Hi all,

Please help me to count "0" consecutive value instead 1 of value
Ex:
1 0 0 0 1 = 0
0 0 1 0 1 = 2
0 0 0 0 1 = 4
Thanks

**Tony Valko** · 12-11-2016, 03:49 PM

We would like to help you but the forum owner prefers that you start your own thread rather than posting your question in someone else's thread (even though it might be related).

**okido** · 12-11-2016, 10:46 PM

Originally Posted by Tony Valko

We would like to help you but the forum owner prefers that you start your own thread rather than posting your question is someone else's thread (even though it might be related).

Dear Tony,

I think it is the same, I just replace 1 by 0. But I can't write a right fomular for this requestion. Please help me. Thanks