Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Please Help!!

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hello all,

    I hope this question makes sense. I have a column that is filled with numbers coming from an IF statement.

    The IF statement either returns a value or "".

    I want to count the number of cells that have values, and not the "", but I keep getting "" returned as a value.

    Do any of you gurus know how to count just the cells with numbers in them and/or turn the "" into something else that is not counted in the COUNT formula???

    THANKS!!

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not use the COUNTIF function?

  3. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ant

    You can use the counta() function

    this function count only cells with values,text,or logical values (true or false)

    Hope that helps

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    something seems fishy here. You only want to count the numbers and by default count only counts numbers. Are you sure you're populated with a "" and your range is okay???

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default


    COUNT ignores "" even if generated by a formula and cannot return "" as result.


  6. #6
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've been looking for guys like you!!

    Thanks for the suggestions.

    I believe my range is correct.

    I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.

    Basically, I am trying to get a total count; I imagine the process would go:
    1) Check the value in the cell.
    2) If the value is "", dont count it, otherwise, count it and move to the next cell.
    3) At the end of say 25 cells, I should have the total of all the cells with actual values.

    Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...

    Thanks again!

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-10 14:33, ant wrote:
    I've been looking for guys like you!!

    Thanks for the suggestions.

    I believe my range is correct.

    I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.

    Basically, I am trying to get a total count; I imagine the process would go:
    1) Check the value in the cell.
    2) If the value is "", dont count it, otherwise, count it and move to the next cell.
    3) At the end of say 25 cells, I should have the total of all the cells with actual values.

    Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...

    Thanks again!
    Microsoft Excel - COUNT.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    E5=
    ABCD
    1Numbers Result of Counting
    2:alert('=IF(B2,1,"")')> :alert('=COUNT(A2:A6)')>3
    31
    43
    5:alert('=IF(B2,1,"")')>
    612
    7
    Sheet1

    You can see the formula of cells only click each above hyperlinks

    The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


    This is intended to convince you that COUNT really ignores blanks.

    A2 and A5 are blanks, generated using an IF formula.


  8. #8
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I see my problem now.

    My if statement: =SE(H14="sales";U14;"") is returning - in the cell for "".

    Is this a formatting problem??

    Thanks again.

  9. #9
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    my example works

    Microsoft Excel - HtmlMaker.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    A1=
    ABCDE
    1
    2
    3 sales:alert('12345')>=IF(B3="sales",C11,"")
    4 no sales:alert('')>=IF(B4="sales",C12,"")
    5
    6
    7
    8
    9
    10
    11 12345
    12
    Sheet1

    You can see the value of cells only click each above hyperlinks



    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-10 15:12, ant wrote:
    I think I see my problem now.

    My if statement: =SE(H14="sales";U14;"") is returning - in the cell for "".

    Is this a formatting problem??

    Thanks again.
    What is the value in U14?



Some videos you may like

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
  •