Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    determine number of rows in a dataset (Excell 2003)

    Hi,

    I know there is a formula for this but I can't for the life of me remember it or its name. I import data into a worksheet. I want to put a forumula in another worksheet which will countif .... a value occurs in the dataset, and the number of rows in the data set might vary.... can anyone help

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: determine number of rows in a dataset (Excell 2003)

    If you are trying to count things on Sheet1 for the value of 47:

    =COUNTIF(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$ A),COUNTA($1:$1)),47)

    It presumes that the dataset starts in A1 and that the dataset is continuous in row1 and in column A (neither have blanks within the dataset).

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: determine number of rows in a dataset (Excell 2003)

    Brilliant thanks !!

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: determine number of rows in a dataset (Excell

    Count dataset for the value of 47:

    Please try this formula,

    the formula can work in non-continuous ranges and blank cells :

    =COUNTIF(OFFSET($A$1,0,0,MATCH(9.99E+307,$A:$A),MA TCH(9.99E+307,$1:$1)),47)

    Regards
    Bosco

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: determine number of rows in a dataset (Excell

    Note: This may not work correctly if there are text values in Column A or in row 1...

    Steve

Posting Permissions

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