# Thread: Number of consecutive cells in a row

1. ## Number of consecutive cells in a row

Hi all

I have an excel table that looks like this

 B C D E F 1) 0 11 4 1 0 2) 0 2 5 9 1 3) 3 4 9 0 0 4) 2 6 3 5 3 5) 8 7 0 1 0 6) 4 6 5 1 0 7) 10 3 2 1 0 8) 0 0 0 16 0

(Letters are column letters; 1) etc are row numbers)

What I want is a formula that lets me know the range of cells that are larger than 0 .
So I'd get
1) = 3
2) = 4
3) = 3
4) = 5
5) = 4 (despite the 0 in the middle)
7) = 4
8) = 1

Can anyone help me out with this?

Much appreciated!

2. Maybe:

=COUNTIF(range,">0")

3. Originally Posted by AliGW
Maybe:

=COUNTIF(range,">0")

Thanks, but it doesn't work - row 5 would result in 4, instead of 5.
Sorry, maybe I have not explained it clearly!

4. No, you haven't!

What's the logic of your suggested answer for row 5?

5. Originally Posted by AliGW
No, you haven't!

What's the logic of your suggested answer for row 5?
I need therange of cells - not sure if this is the correct English word for it.
So I want the number of cells starting from the first value larger than 0, ending with the last value larger than 0 .
So if
A1 = 3
B1 = 0
C1 = 2

The result should be 3.

6. If I understand you correctly, then this formula will work : =(MAX(IF(B1:F1>0,COLUMN(B1:F1),"")))-1. The " -1 " at the end of the formula simply subtracts the column which houses the formula itself. I assume my formula to be in A1 ( Column A ).

7. Optional response to your true meaning : =(MAX(IF(B1:F1>0,COLUMN(B1:F1),"")))- COUNTIF(B1:F1,0)

#### Posting Permissions

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