Counting Consecutive Values

1. Counting Consecutive Values

I have data in rows which contain IF statements that result in a 1 for true or 0 for false. I need to count the number of consecutive 1 values until a 0 is hit. For example:

A B C D E F G
1 1 1 0 1 0 3
1 1 0 0 0 0 2
1 0 1 1 1 1 1
0 1 0 0 1 1 0

My result is in column G.

Any suggestions?
Thank you!!

2. Re: Counting Consecutive Values

Does this do it?

=MATCH(0,A1:F1,0)-1

copied down

3. Re: Counting Consecutive Values

Yes that worked, except when all values in the string are 1 it is returning #N/A. Is there a way to fix that?

4. Re: Counting Consecutive Values

So you want 6 returned instead?

If yes, try:

=IFERROR(MATCH(0,A1:F1,0)-1,COUNT(A1:F1))

5. Re: Counting Consecutive Values

That's exactly what I needed - Thank you for the quick response!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1