Results 1 to 7 of 7

Thread: Number of consecutive cells in a row

  1. #1
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    36
    Articles
    0
    Excel Version
    Version 2007

    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!

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,652
    Articles
    0
    Excel Version
    Office 365 Subscription
    Maybe:

    =COUNTIF(range,">0")
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    36
    Articles
    0
    Excel Version
    Version 2007
    Quote Originally Posted by AliGW View Post
    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. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,652
    Articles
    0
    Excel Version
    Office 365 Subscription
    No, you haven't!

    What's the logic of your suggested answer for row 5?
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    36
    Articles
    0
    Excel Version
    Version 2007
    Quote Originally Posted by AliGW View Post
    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. #6
    Seeker Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    Microsoft Excel Office 365
    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. #7
    Seeker Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    Microsoft Excel Office 365
    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
  •