OK. I need help to create this excel formula..?

Question

Ok Start with this concept and see if you can help me write a formula:

The word HELP is in cell A1

I have the following formula in cell A2
=if(A1="HELP","GOOD","BAD")

So if the word help is in cell A1, cell A2 will return the word good,
If not, cell A2 will return the word bad.


Now, I need a formula that will be able to determine if the word
help is written anywhere in a given sheet!!!!, and then return
good or bad...

Any Ideas greatly appreciated!!!

Answer 1

If the word "Help" will be in a cell by itself, with no other characters, then try this:

=IF(COUNTIF (Sheet1!1:65535,"Help" )>0,"Good","Bad")

Answer 2

You can use the countif function to count the number of times the word help exists in a range.

=COUNTIF(A1:B200,"HELP")

If you are looking for HELP as a substring (for example a cell contains I NEED HELP), you would then need to use the find function first

=FIND("HELP",A1)

You could then put is together with the iserr function to make it display a little nicer

=IF(ISERR(FIND("HELP",A4)),FALSE,TRUE)

You can then use the first function to count the number of TRUE values

=COUNTIF(B1:B100,TRUE)