Announcement

Collapse
No announcement yet.

Count number of cells/rows until first blank cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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
    Kris

    ExcelFox

    Comment


    • #3


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

      Krishnakumar,

      Thanks for that code, it worked perfectly.

      -Pat

      Comment

      Working...
      X