Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting certain cells correctly (Excel 2000)

    I am getting the opposite answer in my formula from what I need. Could someone look at my formula and see what I have backwards or wrong. I tried throwing the NOT command in there, but to no avail... I have 160 records with LWCHRG in 16 of them. I am trying to count just those cells. However, am getting the opposite answer of 144.

    Formula is:

    =COUNT(IF(ISERROR(FIND("LWCHRG",D1160)),0)) and pressing CTRL SHIFT and ENTER

    attaching file for reference.

    thank you, and most of all for your patience today...really rough day and I know we all have them.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: counting certain cells correctly (Excel 2000)

    Try this one:

    =COUNT(IF(NOT(ISERROR(FIND("LWCHRG",D1160))),0))

    as an array formula (Ctrl+Shift+Enter). You could also have used your original formula:

    =COUNTA(D1160)-COUNT(IF(ISERROR(FIND("LWCHRG",D1160)),0))

    also as an array formula.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting certain cells correctly (Excel 2000)

    John,

    Bless you. That worked fine. But I must know...why the ISNUMBER, LWCHRG is not formatted as a number...

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: counting certain cells correctly (Excel 2000)

    Try

    =COUNT(IF(ISNUMBER(FIND("LWCHRG",D1160)),0)) with Ctrl-Shift-Enter

    =SUM(IF(ISNUMBER(FIND("LWCHRG",D1160)),1,0)) with Ctrl-Shift-Enter also works
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting certain cells correctly (Excel 2000)

    Hans,

    Thank you so much. Both formulas work as well as Johns. I had the NOT in the wrong place when I was trying it out...I'll get this after a while, you just wait and see. [img]/forums/images/smilies/smile.gif[/img] Because of all you good folks help.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting certain cells correctly (Excel 2000)

    John,

    Thank you for the good explanation. I'm learning all kinds of good stuff today.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: counting certain cells correctly (Excel 2000)

    Because FIND returns the position number of the found string within the target string if it finds it, and returns #VALUE! if it doesn't find it. ISNUMBER handles the numbers by returning them as 'TRUE', and treats everything else, including #VALUE! , by returning them as 'FALSE'.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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