Cell Contains Word

[Tracey]

Hi

I have the following formula but am having some difficulty with the last bit.

If cell G7 contains the word "Total" I want the result to be 0 if the rest
of the criteria is not met.

so if cell G7 = "London Total" I want the result to be 0

=IF(A7="Y",COUNTIF(J7:Q7,"(blank)"),IF(A7="N",10,IF(SEARCH("Total",G7),0,0)))

the rest of the formula works fine...

Many thanks for your help!

 

[vezerid]

The expression

ISNUMBER(SEARCH("total",G7))

Will return TRUE if "total" is anywhere in G7. Case insensitive. FIND
is case-sensitive.

HTH
Kostis Vezerides

 

[T. Valko]

=IF(A7="Y",COUNTIF(J7:Q7,"(blank)"),IF(A7="N",10,IF(SEARCH("Total",G7),0,0)))

There's a logic "problem" with your formula.

If the first 2 IFs are false then the last IF returns 0 whether G7 contains
"total" or not.

 

[Harlan Grove]

ISNUMBER(SEARCH("total",G7))

....

COUNTIF(G7,"*Total*")

would be more efficient (shorter, faster, uses less memory in RAM and
on disk) for case insensitive searches. It returns 1 if Total occurs
in the value of G7, 0 otherwise, and Excel treats 1 as TRUE and 0 as
FALSE in the 1st argument to IF.

For case sensitive searches,

COUNT(FIND("Total",G7))

is shorter than the ISNUMBER alternative. ISNUMBER is better when you
need to return an array.

 

[vezerid]

Thanks Harlan. I am always impressed by your posts.

Kostis