How do I count consecutive zero's in rows and exclude blanks

**Sloxi** · 03-13-2019, 07:36 AM

My data starting at column a1 to ??

How do I count consecutive zero's in rows and exclude blanks? (2 or more zeroes)

SR 12, 12, 11, 0, 0, 0, 12, 11, 7, 0, 1, 10, 0, 0, 13, 0, 45, 11, 17, 0, 23, 7, 9, 0, 0 = 3
PP 12, 1, 10, 12, 11, 7, 0, 0, 1, 10, 0, 0, 13, 0, 45, 11, 17, 0, 23, 7, 9, 0, 0 = 3

**Special-K** · 03-13-2019, 07:42 AM

How do you get the answer 3 in the PP row ?
I see only two consecutive 0s in there

**Roel Jongman** · 03-13-2019, 07:45 AM

@special K there are 3 sets in PP, 2nd one is a bit harder to spot..

PP 12, 1, 10, 12, 11, 7, 0, 0, 1, 10, 0, 0, 13, 0, 45, 11, 17, 0, 23, 7, 9, 0, 0 = 3

**Phuocam** · 03-13-2019, 07:53 AM

Try this:

=SUM(--(FREQUENCY(IF((A1:Z1=0)*(A1:Z1<>""),COLUMN(A1:Z1)),IF((A1:Z1<>0)*(A1:Z1<>""),COLUMN(A1:Z1)))>1))

**Array formula,enter with Ctrl+Shift+Enter

**Special-K** · 03-13-2019, 07:56 AM

Originally Posted by Roel Jongman

@special K there are 3 sets in PP, 2nd one is a bit harder to spot..

PP 12, 1, 10, 12, 11, 7, 0, 0, 1, 10, 0, 0, 13, 0, 45, 11, 17, 0, 23, 7, 9, 0, 0 = 3

Ah I thought we were counting longest winning streak of 0s

**Sloxi** · 03-13-2019, 08:04 AM

Thank you for helping, Im getting a formula error message after changing the range -Is Office 2007 ok?

**Phuocam** · 03-13-2019, 08:54 AM

Try changing the separator "," to ";"

**Sloxi** · 03-14-2019, 06:05 AM

Thank you, perfect - much appreciated

How do I count consecutive zero's in rows and exclude blanks

LinkBack

Thread Tools

Rate This Thread

Display

How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Re: How do I count consecutive zero's in rows and exclude blanks

Thread Information

Users Browsing this Thread

Similar Threads

Count unique values with criteria and blanks and an average of maximum consecutive values

[SOLVED] Using COUNTA to count a range but exclude blanks and 0.00

Count consecutive more than 3 days absent in a month/year exclude Sat/Sun/PubHol

Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Count values in column but exclude filtered rows

count number of rows with data but only if there are 5 consecutive rows or more

Over Different Rows - count the number of consecutive values >=2

Bookmarks

Bookmarks

Posting Permissions