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,I F(SEARCH("Total",G7),0,0)))

the rest of the formula works fine...

Many thanks for your help!

The expression

ISNUMBER(SEARCH("total",G7))

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

HTH
Kostis Vezerides

On Nov 19, 6:31*pm, Tracey wrote:
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,I F(SEARCH("Total",G7),0,0)))

the rest of the formula works fine...

Many thanks for your help!

=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.


--
Biff
Microsoft Excel MVP


"Tracey" wrote in message
...
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,I F(SEARCH("Total",G7),0,0)))

the rest of the formula works fine...

Many thanks for your help!

vezerid wrote...
....
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.

Thanks Harlan. I am always impressed by your posts.

Kostis

On Nov 19, 9:12*pm, Harlan Grove wrote:
wrote...

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