I am trying to figure out how to write a formula where I do multiple steps. The 1st step would be to subtract, then reference another column looking for 3 words and if any cell in that column contains any of the 3 words then subtract one. For example =(M5-K5)...is the first part of the formula then I want excel to reference column F looking for the words "good", "exact" or "ranch" and if any cell in column F contains those words then subtract 1 from (M5-K5). If no cell in column F contains those words then just (M5-K5). Can anyone help me?

You can't put "multiple formulas" in one cell. Strictly speaking, you can't even get a formula to do "multiple things". A formula can only do one thing: return a result

Now, it may perform multiple calculations on the way to returning that result, but in the end it really only did 1 thing: returned a result.

That said, you really don't want =(M5-K5) to be the first step, you actually want that to be the

laststep.If I understand your request correctly, you want to return M5-K5-1 if any of the three words are in Columns F and return M5-K5 if not.

The IF function is designed to do a "logical test" and return one result if the test returns TRUE and an different result if the test returns FALSE.

=IF(

logical_test, value_if_true, value_if_false)In your case, the logical test is:

Does Column F contain "good" or "exact" or "ranch"?

We can determine if that is the case by "counting" the number of times each word occurs. If the count is greater than 0 for any of those words, then at least one of those words exists in Column F.

The

value_if_trueis (M5-K5-1)The

value_if_falseis (M5-K5)Putting that all together we get:

=IF(OR(COUNTIF(F:F,"good")>0, COUNTIF(F:F,"exact")>0,

COUNTIF(F:F, "ranch" )>0),M5-K5-1,M5-K5)Note: the formula has been split into 2 lines to make it easier to read.

Wow...this works perfectly and thank you for the detailed explanation...greatly appreciated!

Glad to have been of assistance.

Ask Your Question

Weekly Poll