countif formula

[japc90]

I am attempting to write a formula that will count cells containing the
word "out" and ignore all others, even if they contain text. The
formula I have so far is, =COUNTIF(B6:E42,"*out").
It does not appear to be working. Can anyone offer any advise? Thank you

 

[Guest]

=COUNTIF(B6:E42,"out")

You don't want a period after it or the star inside the quotes. Now this
will only work if only the word out is in the cell. Is the data just going
to have out or will there me more words in the cells? (example: 'Over and
out'

 

[Bob Phillips]

That should work, but it won't catch say 'get out of here', for that you
need

=COUNTIF(B6:E42,"*out*")

and it won't count say 'the ins and outs is not out' twice, for that you
need

=SUMPRODUCT(LEN(B6:E42)-LEN(SUBSTITUTE(B6:E42,"out","")))/3

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[AntnyMI]

Wouldn't this also included unwanted results such as "abOUT" and "OUTer
space"?

 

[Bob Phillips]

It will include them, but it is up to the OP to decide whether that is
unwanted or not.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[japc90]

It will definitely need to include other words than just "out." It is
being used for an employee calendar so it will have the employees name
in the field as well.

Thank you.

 

[Bob Phillips]

The questions are:

- could a cell hold more than one instance of out, and if so should they all
be counted?
- should about be counted or not?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)