Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTIF <> '' (2002 SP3)

    Hi y'all
    Apart from =SUMPRODUCT((LEN(A1:A35)<>0)*1) can anyonne think of a way (using COUNTIF maybe) that I can count the cells in a column that are not equal to ""

    TIA
    Regards
    Paul

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: COUNTIF <> '' (2002 SP3)

    I would enter an array function:

    =sum(if(A$1:A$35 <> "",1) formula has to be entered with ctrl-shift-enter

    since I could change the test to be = "Doug" or >5 etc. at some later date

    On the other hand, if I know it is just non-blanks I would ever be worried about,

    =counta(A$1:A$35)

    will work, too...

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF <> '' (2002 SP3)

    <hr>=sum(if(A$1:A$35 <> "",1)<hr>
    That is equivalent to =SUMPRODUCT((A1:A35<>"")*1) since SUMPRODUCT is an implicit array function.

    <hr>=counta(A$1:A$35)<hr>
    won't work because the cells are equal to "" (from an if statement) rather than blank.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: COUNTIF <> '' (2002 SP3)

    <P ID="edit" class=small>(Edited by Jezza on 11-Mar-06 23:11. To add PS)</P>Hi Paul

    I think this is what you are wanting, I have tried it out with a range that contained a couple of if statements and some blanl cells and it seems to work:

    =(ROWS(A1:A35)*COLUMNS(A1:A35))-COUNTBLANK(A1:A35)


    Good luck

    PS
    If it is just Column A with this then it can be further simplified:

    =ROWS(A:A)-COUNTBLANK(A:A)
    Jerry

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF <> '' (2002 SP3)

    =COUNT(Range)+COUNTIF(Range,"?*")
    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF <> '' (2002 SP3)

    Hello Aladin,

    Unless I'm mistaken, your formula doesn't take TRUE/FALSE values into account ( I assume that the original poster wants to count those as not "")

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF <> '' (2002 SP3)

    Hoi Hans,

    That's right.

    Closer to the SumProduct formula in behavior:

    =COUNTA(Range)-COUNTIF(Range,"")

    The error values would also be counted in by the foregoing formula.

    However, I just assumeed that only text and numeric values would be of interest.

    Aladin
    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNTIF <> '' (2002 SP3)

    Yes, if only text and number values are of interest, the formula from your previous reply will do the job nicely. Otherwise, Jezza's formulas are probably best, because they cover all situations. COUNTA doesn't count really blank cells (it does count cells that are blank as the result of a formula).

  9. #9
    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: COUNTIF <> '' (2002 SP3)

    <hr>COUNTA doesn't count really blank cells (it does count cells that are blank as the result of a formula).<hr>

    They are not really blank if they contain a formula. A blank cell is an empty cell. If a formula is in a cell, it is not empty. A null string is a zero length string. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF <> '' (2002 SP3)

    Thanks for all the replies guys.

    In this application the cells that I am interested in have a formula that return text or "". Sad @#$!% that I am, I figured out whilst trying to get to sleep last night that =COUNTA(A:A,"?*") should get me what I need for this. However I shall store Jezza's and Aladin's ideas away for future use.

    Thanks once again.

Posting Permissions

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