Forum Discussion
Solved
Multiple IF function
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 ...
Another less straight forward solution:
=(1-MMULT({0.35,1},COUNTIF(D3,{"*CR*";"*INV*"})))*I3
One more problem I just realized... if it finds INV in field D3, to make it come back with a zero?
Another less straight forward solution:
=(1-MMULT({0.35,1},COUNTIF(D3,{"*CR*";"*INV*"})))*I3- You're a genius! Thank you so much!! Truly, thank you!!
Or with a lookup table:
=LOOKUP(2,1/COUNTIF(D3,"*"&{"";"CR";"INV"}&"*"),{1;0.65;0})*I3