+ Reply to Thread
Results 1 to 4 of 4

Count number of cells between two cells with a positive value.

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2007
    Posts
    9

    Count number of cells between two cells with a positive value.

    I'm desperately looking for a formula which will return the same values I have in line 9 of my example.xls sheet.

    It simply returns the number of cells between two cells in a timeline, which actually have values (not zero), like in line 4.

    So as an example: there is a value in E4 and then zeros until J4. I need the cells in E9 to I9 to tell me that there are 5 periods between the two.

    Thanks a lot for your help.
    example (1).xlsx

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Count number of cells between two cells with a positive value.

    Hi,

    Not quite sure how you arrived at your value of 0 for Jan, so I just added a simple clause which will default the first return to 0, but this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in D9 and copied across should match your desired results:

    =IF(COLUMNS($A:A)=1,0,1+INDEX(FREQUENCY(IF($D$4:$Z$4=0,COLUMN($D$4:$Z$4)),IF($D$4:$Z$4<>0,COLUMN($D$4:$Z$4))),1+COUNTIF($D$4:D$4,">0")))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    10-17-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Count number of cells between two cells with a positive value.

    Very impressed! thank you!

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Count number of cells between two cells with a positive value.

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trim Range of Cells FIRST then Count the number of blank cells
    By Jim28 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2013, 04:08 AM
  2. Replies: 2
    Last Post: 07-25-2013, 01:01 PM
  3. [SOLVED] Count number of cells that contain text but exclude cells that contain quotation marks
    By Cantyman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2012, 03:57 PM
  4. Replies: 2
    Last Post: 03-19-2012, 09:53 AM
  5. formula to copy last positive number in range of cells
    By rolan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2005, 10:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1