Formula confusion !!!!
 

I would like a formula that would give rise to a different result 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 thanks

Formula confusion !!!!
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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

=ISNUMBER(SEARCH("X",D2:D50)) [if true then]

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

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

Again thankyou
Stu

Formula confusion !!!!
 

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",D2:D50)) [if true then]

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

=CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ON E"," 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,"O K","")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[quote=Claus Busch;1599708]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",D2:D50)) [if true then]

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

=CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ON E"," 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,"O K","")


Regards
Claus Busch
--

Thank you for your time claus,

what i am trying to do is search my inventory, i currently have three working formulae , but in separate boxes and wished to combine these. I tried your new formula but it returns a blank cell always. I've tried to 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 be 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 conditions are false.

Again i appreciate your help.

Stu

[quote=stu28;1599817]

Formula confusion !!!!
 

Hi Stu,

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

what i am trying to do is search my inventory, i currently have three
working formulae , but in separate boxes and wished to combine these. I
tried your new formula but it returns a blank cell always. I've tried to
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 be
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 conditions
are false.

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","TH REE"),"")))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Formula confusion !!!!
 

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","TH REE"),"")))

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","TH REE"),"")))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

I appreciate your time, thanks again

Stu