Multiple IF conditions using AND, OR, NOT

[johobie]

Data:

Row​	A​	B​	C​	D​	E​
1​	Acquisition	CALL	Joheen	-3,000.00	3,000.00
2​	Buy	CALL	Cure	-5,266.50	5,266.50
3​	Buy		Afterburn	-619.50

Formula not working:
=IF(ISNUMBER(SEARCH("Acquisition",$A1))*OR(ISNUMBER(SEARCH("Buy",$A1)))*NOT(ISNUMBER(SEARCH("CALL",$B1))),-$D1,"")

I want to solve for:
If False: If A1 contains "Acquisition" OR if A1 contains "Buy" and B1 does NOT contain "CALL", then fill E1 with no value ("").
If True: If A1 contains "Acquisition" OR if A1 contains "Buy" and B1 does contain "CALL", then fill E1 with negative value of D1.

 

[jasonb75]

This is what you have asked for so far, but what if A1 does not contain either "Acquisition" or "Buy"?

=IF(OR(ISNUMBER(SEARCH({"Buy","Acquisition"},$A1))),IF(ISNUMBER(SEARCH("CALL",$B1)),-$D1,""))

 

[henrik2h]

Depending if A only have the given value and not part of a string

=IF(AND(OR(A1=“Acquisition”,A1=“Buy”),B1<>”CALL”),””,-D1)

 

[johobie]

This is what you have asked for so far, but what if A1 does not contain either "Acquisition" or "Buy"?

=IF(OR(ISNUMBER(SEARCH({"Buy","Acquisition"},$A1))),IF(ISNUMBER(SEARCH("CALL",$B1)),-$D1,""))

Thank you for the quick reply!
You are right, any suggestion on how I fill E1 with no value should A1 contain either another text OR nothing?
This is getting above my grade level.

 

[johobie]

Depending if A only have the given value and not part of a string

=IF(AND(OR(A1=“Acquisition”,A1=“Buy”),B1<>”CALL”),””,-D1)

Tried the above and not getting value or no value:
A1 contains Buy, B1 does not contain CALL. Result is: #name?
A1 contains Buy, B1 does contain CALL. Result is: #name?
A1 contains Acquisition, B1 contains CALL. Result is #name?
A1 contains Acquisition, B1 does not contain CALL. Result is #name?

 

[johobie]

Depending if A only have the given value and not part of a string

=IF(AND(OR(A1=“Acquisition”,A1=“Buy”),B1<>”CALL”),””,-D1)

Thank you for the quick reply! It is not part of a string.
You are right, any suggestion on how I fill E1 with no value should A1 contain either another text OR nothing?
This is getting above my grade level.

 

[jtakw]

Depending if A only have the given value and not part of a string

=IF(AND(OR(A1=“Acquisition”,A1=“Buy”),B1<>”CALL”),””,-D1)

Tried the above and not getting value or no value:
A1 contains Buy, B1 does not contain CALL. Result is: #name?
A1 contains Buy, B1 does contain CALL. Result is: #name?
A1 contains Acquisition, B1 contains CALL. Result is #name?
A1 contains Acquisition, B1 does not contain CALL. Result is #name?

Hi,

You need to Re-type henrik2h formula from post # 3 using proper quote marks, e.g. "
He was probably not on a computer at the time, hence the quote marks are "slanted"

=IF(AND(OR(A1="Acquisition",A1="Buy"),B1<>"CALL"),"",-D1)

 

[jtakw]

It is not part of a string.
any suggestion on how I fill E1 with no value should A1 contain either another text OR nothing?
This is getting above my grade level.

This should do what you want:

Book3.xlsx
	A	B	C	D	E
1	Acquisition	CALL	Joheen	-3,000.00	3000
2	Buy	CALL	Cure	-5,266.50	5266.5
3	TBD	CALL	Afterburn	-619.5
4		CALL	Cure	-5266.5
5	Acquisition		Joheen	-3000
Sheet966

Cell Formulas
Range	Formula
E1:E5	E1	=IF(AND(OR(A1={"Acquisition","Buy"}),B1="CALL"),-D1,"")

 

[johobie]

Hi,

You need to Re-type henrik2h formula from post # 3 using proper quote marks, e.g. "
He was probably not on a computer at the time, hence the quote marks are "slanted"

=IF(AND(OR(A1="Acquisition",A1="Buy"),B1<>"CALL"),"",-D1)

Thank you jtakw! I did not see the quotations were different.
Thank you henrik2h!
Thank you jasonb75!

 

[jtakw]

any suggestion on how I fill E1 with no value should A1 contain either another text OR nothing?

You're welcome, you should use my formula from Post # 8 and Not from #7, they are not the same, due to your latest requirements underlined and Bold above.