+ Reply to Thread
Results 1 to 8 of 8

Counting blank cells between two values

  1. #1
    Registered User
    Join Date
    07-07-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Counting blank cells between two values

    Hi Guys,

    Couldnt find anything on the board or via Google. Using 2010:

    Hope I explain this well enough. A single column either has a blank or some number (consider it just a random number).

    I want to count the blank cells between the last and second last value in the second column. And in a different results cell, count the blanks from the second to last value to the third to last value. These 2 results cells will continue to use the last, second, and third to last values as I continue to enter more blanks or numbers as I update the worksheet.

    IE
    blank
    blank
    blank
    3
    blank
    blank
    4
    blank
    10
    blank
    blank
    blank
    blank
    blank
    blank
    20

    Thanks!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Counting blank cells between two values

    last and second last
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    second last and third last
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by martindwilson; 07-18-2013 at 03:31 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    07-07-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Counting blank cells between two values

    Wow! Thanks! I would have never figured that out. Can you explain a bit to me how you got this so I can actually learn it?

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Counting blank cells between two values

    index(($a$2:$a$1000<>"")*row($a$2:$a$1000) gives 1 for where $a$2:$a$1000 <>1
    do it a bit smaller a2:a6
    index(($a$2:$a$6<>"")*row($a$2:$a$6)
    now say

    a2 blank
    a3 text or something
    a4 blank
    a5 blank
    a6 something
    that would give {0,1,0,0,1} multiply that by row($a$2:$a$6)
    note
    [row($a$2:$a$6) produces an array of the row numbers ie {2,3,4,5,6} you could use row(a1:a5) ie {1,2,3,4,5)
    or even row(z3:z7) {3,4,5,6,7} we just want an array of ascending values the same size as the array a2:a6}
    so
    {0,1,0,0,1} *row($a$2:$a$6) ={0,1,0,0,1} *{2,3,4,5,6} ={0,2,0,0,5} each item is multiplied by its corresponding item
    so we now have
    index({0,2,0,0,5},0)
    so take the large 1 & 2
    large( index({0,2,0,0,5},0),1) is 5
    large ( index({0,2,0,0,5},0),2) is 2
    5-2 is 3 inclusive so subtract 1 to get the number of blanks
    Last edited by martindwilson; 07-19-2013 at 07:28 AM. Reason: edit for typos

  5. #5
    Registered User
    Join Date
    07-07-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Counting blank cells between two values

    Thank you so much! I had no idea what the "$a$2:$a$1000 <>1" part was doing. Makes sense and I would never have figured this out on my own

  6. #6
    Registered User
    Join Date
    09-10-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting blank cells between two values

    Apologies for reopening this Solved thread...

    How about if counting blank cells between 2 values from the top. The expected answer in the example below would be 3.


    Column Heading
    blank
    blank
    blank
    71
    blank
    blank
    blank
    blank
    50
    blank
    blank
    blank
    blank
    blank
    blank

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Counting blank cells between two values

    Jeff Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    09-10-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting blank cells between two values

    Will do (and I figured I was violating a rule...should've looked it up!)

+ 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. Counting blank cells
    By matthew.lawson in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-04-2013, 10:18 AM
  2. Replies: 3
    Last Post: 07-03-2009, 03:13 AM
  3. counting blank cells
    By nralph13 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-30-2006, 07:06 PM
  4. Counting blank cells
    By geoff1234 in forum Excel General
    Replies: 7
    Last Post: 07-10-2006, 06:29 AM
  5. Replies: 0
    Last Post: 08-23-2005, 03:43 AM

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