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!

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

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 ).

Posting Permissions

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