# Thread: Number of consecutive cells in a row

1. ## Number of consecutive cells in a row

Register for a FREE account, and/
or Log in to avoid these ads!

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!  Reply With Quote

2. Maybe:

=COUNTIF(range,">0")  Reply With Quote

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!  Reply With Quote

4. No, you haven't!

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

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.  Reply With Quote

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 ).  Reply With Quote

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

#### Posting Permissions

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