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
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})*I3Detlef, great!
Just for the collection the equivalent of MMULT
=(1-SUMPRODUCT(COUNTIF(D3,{"*CR*","*INV*"})*{0.35,1}))*I3SUMPRODUCT() was my first attempt, Sergej.
But I like MMULT() more than SUMPRODUCT().
:-)
