Multiple IF function

[katie]

I am trying to apply appropriate commissions based on finding "CR" in a column.

So far, I have: =SUM((ISNUMBER(SEARCH("CR",D3))*(I3*0.65))) - which if it finds CR in field D3, it multiples field I3 by .65. 

I need it to then determine that if CR is not found, to multiply field I3 by 1.

Is that possible?

[#2]

Hi Katie,

Perhaps something like

=I3*IF(ISNUMBER(SEARCH("CR",D3)),0.65,1)

[#3]

Hi

Straight forward:

=IF(COUNTIF(D3,"*CR*"),0.65,1)*I3

Less straight forward:

=(1-COUNTIF(D3,"*CR*")*0.35)*I3

[#4]

Thank you, thank you for the quick reply! That is exactly what I needed! :)

[#5]

Thank you so much for the quick reply! Amazing that I have spent hours trying to figure this out! Thank you! :)

[#6]

One more problem I just realized... if it finds INV in field D3, to make it come back with a zero?

[#7 · Accepted solution]

Another less straight forward solution:

=(1-MMULT({0.35,1},COUNTIF(D3,{"*CR*";"*INV*"})))*I3

[#8]

You're a genius! Thank you so much!! Truly, thank you!!

[#9]

Or with a lookup table:

=LOOKUP(2,1/COUNTIF(D3,"*"&{"";"CR";"INV"}&"*"),{1;0.65;0})*I3

[#10]

Detlef, great!

Just for the collection the equivalent of MMULT

=(1-SUMPRODUCT(COUNTIF(D3,{"*CR*","*INV*"})*{0.35,1}))*I3

[#11]

You guys are unbelievable! THANK YOU!! :)

[#12]

SUMPRODUCT() was my first attempt, Sergej.

But I like MMULT() more than SUMPRODUCT().

:)

[#13]

Yes, more sexy

[#14]

Re: =(1-MMULT({0.35,1},COUNTIF(E2,{"*CR*";"*INV*"})))*G2

Found a little problem that I need more help with. :)

If column E2 has CR and INV, then it comes back with a negative number. Is there a way to add the If the column has both CR and INV, then come back with zero, not negative?

[#15]

Katie, as variant that could be

=(1-MIN(MMULT({0.35,1},COUNTIF(E2,{"*CR*";"*INV*"})),1))*G2

[#16]

That works perfectly!! Thank you so much Sergei! :)

[#17]

It is sometimes frustrating for the helpers if you keep extendig the scope of the problem.

So, please always present the full scope of the problem in the first post.

=MAX(0,(1-MMULT({0.35,1},COUNTIF(E2,{"*CR*";"*INV*"}))))*G2

[#18]

I totally understand, however I didn't realize these issues would arise until I ran the formula. ;)