Change Sign in a Cell When Another Cell Includes but is not Limited to Certain Text

[jdpro]

Hello

Using the formula: =IF(J43="buy",M43*-1,M43)

I would like to be able to have "buy" and other text in the reference cell and still have it change the result cell to negative when it sees the word "buy". There will be different "buy" items and it would be great to have it in one column rather than having to add another column to show what is being bought. So can a variable be added to the function?

Thanks!

 

[Peter_SSs]

You're welcome.

 

[jdpro]

If I have a follow-up question on this should I make a new thread? Sometimes there are fees involved and I have to enter "Buy Fees" to get the negative cost. It would be nice if the program could change the result to a negative if the cell in the Action column has "Buy" or if it has "Fees." But I'm not sure if this is a separate question that requires a separate thread.

Thanks as always!

 

[Peter_SSs]

But I'm not sure if this is a separate question that requires a separate thread.

The follow-up is very closely related to the original so fine to continue here.

Try

=K43*L43*(1-2*MAX(COUNTIF(J43,"*buy*"),COUNTIF(J43,"*fees*")))

or

=K43*L43*IF(OR(COUNTIF(J43,"*buy*"),COUNTIF(J43,"*fees*")),-1,1)

 

[jdpro]

That works well Peter, I entered the first formula that did the trick

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.