Formula confusion !!!!

[stu28]

I would like a formula that would give rise to a different resul
depending on the number occurrences in a particular cell.

I am currently using the following formula;

=IF(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"MATCH","D")

would like a result of "ONE" if SP or HA or PA occurs in cell

a result of "TWO" if any combination of two matches occur and "THREE
for any combination of three.

Please help !

Many thank

 

[Claus Busch]

Hi Stu,

Am Sat, 10 Mar 2012 17:06:36 +0000 schrieb stu28:

would like a result of "ONE" if SP or HA or PA occurs in cell

a result of "TWO" if any combination of two matches occur and "THREE"
for any combination of three.

try:
=CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2)),"ONE","TWO","THREE")


Regards
Claus Busch

 

[stu28]

Claus, Thank you for a solution to my previous problem. If i may ask
further question - if i were to want an empty cell to return a result o
'an empty cell" would this be possible ?

Also does excel allow a number of formulae in a single cell ?
for example ;

=ISNUMBER(SEARCH("X",D250)) [if true then]

=ISNUMBER(SEARCH("AF",B2:B50)) [if true then]

=CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ONE"," TWO","THREE")

Again thankyou
St

 

[Claus Busch]

Hi Stu,

Am Mon, 12 Mar 2012 02:18:49 +0000 schrieb stu28:

Also does excel allow a number of formulae in a single cell ?
for example ;

=ISNUMBER(SEARCH("X",D250)) [if true then]

=ISNUMBER(SEARCH("AF",B2:B50)) [if true then]

=CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ONE"," TWO","THREE")

what do you want to do?
You could work with nested IF formulas, but in your case you will only
get the result of the first true condition. The other conditions will
not be shown. Or you have to combine the IF-statements with OR or AND.
But if all your conditions have to be true, you can try:
=IF((ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X",D2))+(COUNT(SEARCH({"SP","HA","PA"},C2))>0))=3,"OK","")


Regards
Claus Busch

 

[stu28]

Hi Stu

Am Mon, 12 Mar 2012 02:18:49 +0000 schrieb stu28

Also does excel allow a number of formulae in a single cell ?
for example

=ISNUMBER(SEARCH("X",D250)) [if true then

=ISNUMBER(SEARCH("AF",B2:B50)) [if true then

=CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ONE"," TWO","THREE")

what do you want to do
You could work with nested IF formulas, but in your case you will onl
get the result of the first true condition. The other conditions wil
not be shown. Or you have to combine the IF-statements with OR or AND
But if all your conditions have to be true, you can try
=IF((ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X",D2))+(COUNT(SEARCH({"SP","HA","PA"},C2))>0))=3,"OK",""


Regard
Claus Busc
--

Thank you for your time claus

what i am trying to do is search my inventory, i currently have thre
working formulae , but in separate boxes and wished to combine these.
tried your new formula but it returns a blank cell always. I've tried t
modify

=IF((ISNUMBER(SEARCH("AF",B2:B50))+ISNUMBER(SEARCH("X",D2
D50))+(COUNT(SEARCH({"SP","HA","PA"},C2:C50))>0))="ONE","TWO","THREE"

but "THREE'' is always returned. I need both first conditions to b
true and the third condition to return a result of "ONE" for one match
"TWO" for two, "THREE" for three and a blank cell for none

Ideally i would also like a blank cell if either of the first condition
are false

Again i appreciate your help

St

 

[Claus Busch]

Hi Stu,

Am Thu, 15 Mar 2012 00:31:58 +0000 schrieb stu28:

I hope, that I understood your problem.
Try:
=IF(ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X",D2))<2,"","true "&(IF(COUNT(SEARCH({"SP";"HA";"PA"},C2))>0,CHOOSE(COUNT(SEARCH({"SP";"HA";"PA"},C2)),"ONE","TWO","THREE"),"")))


Regards
Claus Busch

 

[Claus Busch]

Hi Stu,

Am Thu, 15 Mar 2012 08:19:38 +0100 schrieb Claus Busch:

=IF(ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X",D2))<2,"","true "&(IF(COUNT(SEARCH({"SP";"HA";"PA"},C2))>0,CHOOSE(COUNT(SEARCH({"SP";"HA";"PA"},C2)),"ONE","TWO","THREE"),"")))

it is a typo in the formula above. Try:
=IF(ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X",D2))<2,"","true "&(IF(COUNT(SEARCH({"SP","HA","PA"},C2))>0,CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2)),"ONE","TWO","THREE"),"")))


Regards
Claus Busch

 

[stu28]

Claus, thank you very much, the formula not only works perfectly but ha
given me a far greater understanding of excel and building formulae.

I appreciate your time, thanks again

St