counta not working
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: counta not working

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    810
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default counta not working

     
    Hello there
    I have range of cells in column A. some are filled with non numeric value. some have formulas which sometime have "". The formulas are of the type =IF(Z10=2, "", B3)

    I want to count the number of non empty cells. counta does not seem to work as it does not treat the formulas which are giving "" as empty.

    Can anyone help please

  2. #2
    Board Regular
    Join Date
    Feb 2011
    Location
    Rawalpindi, PK
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counta not working

    You could modify your formula to =ROWS(range)-COUNTBLANK(range) or =COUNTA(range)-COUNTBLANK(range)

  3. #3
    Board Regular
    mmmm Pizza
    Sandeep Warrier's Avatar
    Join Date
    Oct 2008
    Location
    Mumbai, India
    Posts
    2,672
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counta not working

    Or

    =SUMPRODUCT(--(LEN(Range)>0))
    Regards,
    Sandeep


    You can post sample data using any one of the following ways:

    1. Richard Schollar's HTML Maker
    2. Excel Jeanie
    3. Border Copy Paste

    Use code tags [CODE]'Your Code[/CODE]
    for posting codes.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,303
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

    Default Re: counta not working

      
    Quote Originally Posted by methody View Post
    Hello there
    I have range of cells in column A. some are filled with non numeric value. some have formulas which sometime have "". The formulas are of the type =IF(Z10=2, "", B3)

    I want to count the number of non empty cells. counta does not seem to work as it does not treat the formulas which are giving "" as empty.

    Can anyone help please
    If the range consists of text values and you want to do a count of non-blank cells (that is, excluding "")...

    =COUNTIF(A2:A100,"?*")

    If there are also numbers to count in:

    =COUNT(A2:A100)+COUNTIF(A2:A100,"?*")
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com