Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    conditional formula (XP)

    Need a formula that looks at a range, counts the number of entries (counta) but EXCLUDE any cells that have the word "Restroom" in them.

    Of course I need this yesterday.

    Please help me - again!

    Thanks

    Linda

  2. #2
    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: conditional formula (XP)

    Hi Linda

    Howsabout

    =COUNTA(range)-COUNTIF(range,"restroom")

    I am sure there are shorter more elegant examples but it works and you want it on the PDQ
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formula (XP)

    Awwwwww man - I was THAT close to having it myself -

    Thanks, Jerry!

    Aunt Linda

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formula (XP)

    I could read the formula just fine - thanks!

    Aunt Linda

  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: conditional formula (XP)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I presume you forgot to change it (since we usually default to "English - Excel"), but in "English-Excel" it should also be a comma (,) not a semi-colon ([img]/forums/images/smilies/wink.gif[/img]

    Steve

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

    Re: conditional formula (XP)

    Formula corrected by HansV - thanks to sdckapr

    Jerry's formula will work fine. It can be done with only COUNTIF:
    <code>
    =COUNTIF(range,"<>restroom")
    </code>
    If you read this in e-mail, the formula may have been mangled, before the word restroom there should be an < (smaller than) and > (greater than) sign without spaces in between.

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

    Re: conditional formula (XP)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Thanks, now corrected!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formula (XP)

    Hans: Your formula is not the same as Jerry's (see the attached file where Jerry's formula is in C1 and yours is in C2). As you can see, your formula includes empty cells in the count and Jerry's does not. From the Original Post, it would appear that Jerry gets the desired answer and you do not.

    In addition, in my example, if nothing had ever been entered in A6:A10, then your formula gets an answer of 4 whereas Jerry's still gets 3.
    Legare Coleman

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

    Re: conditional formula (XP)

    Thanks for your useful comments. Perhaps I shouldn't Lounge immediately after dinner... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: conditional formula (XP)

    When YOUR cells aren't empty? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

Posting Permissions

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