1. ## 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.

Thanks

Linda

2. ## Re: conditional formula (XP)

Hi Linda

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

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

3. ## Re: conditional formula (XP)

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

Thanks, Jerry!

Aunt Linda

4. ## Re: conditional formula (XP)

I could read the formula just fine - thanks!

Aunt Linda

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

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

8. ## 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.

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

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

#### Posting Permissions

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