Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Count number of cells/rows until first blank cell

  1. #1
    Join Date
    22nd June 2010
    Posts
    5

    Count number of cells/rows until first blank cell

    Hey everyone,

    I have a list of product lines in column K, and I would like to count the number of cells, or rows, until a blank cell is reached. There is a blank cell when a total is computed for those product lines.

    I am working with a pivot table in Excel 2003, so the order of the table is organized by name (column J), then by product line. The total (column L) computed is for each person's name and creates a blank cell in column K before the next persons number of product lines begins.

    Since this is a pivot table that will be updated about twice a year, the number of rows will change, which is why I want to count until the first blank cell is hit, then I will have a total for that person, and hopefully this can be done for each person following.

    If there is a function(s) that I can use that would be best, otherwise I can try VBA.

    Thank you in advance!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,337

    Re: Count number of cells/rows until first blank cell

    Hi,

    May be..

    In L3:

    =COUNTA(K3:INDEX($K3:$K$100,MAX(1,MATCH(TRUE,INDEX($K3:$K$100="",0,0),0))))

    L4 and copied down,

    =IF(K4="",COUNTA(K5:INDEX($K5:$K$100,MAX(1,MATCH(TRUE,INDEX($K5:$K$100="",0,0),0)))),"")

    adjust the ranges accordingly.

    HTH

  3. #3
    Join Date
    22nd June 2010
    Posts
    5

    Re: Count number of cells/rows until first blank cell

    Krishnakumar,

    Thanks for that code, it worked perfectly.

    -Pat

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 4
    Last Post: July 17th, 2013, 15:59
  2. Replies: 4
    Last Post: May 1st, 2008, 18:32
  3. Count Cells By Number & Add Adjacent Cell If Number Is X
    By black knight in forum EXCEL HELP
    Replies: 6
    Last Post: January 19th, 2008, 07:42
  4. Replies: 6
    Last Post: June 22nd, 2007, 00:30
  5. Replies: 5
    Last Post: September 30th, 2006, 00:26

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
  •  
porno