SOLVED

# Multiply formula with multiple criteria

Occasional Contributor

# Multiply formula with multiple criteria

I need a formula that allows me to calculate commission based off of several scenarios. If they have less than 3 deals (let's say in cell A1 to make simple) than I need to multiply their revenue (in cell A2) by .75, if over 3 deals, AND their revenue is under \$75K then multiply by .80, if over 3 deals, AND their revenue is between 76K-100K then multiply by .85, if over 3 deals, AND their revenue is over \$100K then multiply by .90. I've tried using IF's, PRODUCT formulas, can anyone help me with this please?

 TOTAL DEALS TOTAL REVENUE FOR AGENT 3 \$                44,250.00

5 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Multiply formula with multiple criteria

=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))

# Re: Multiply formula with multiple criteria

AMAZING!!! Thank you so much! That was driving me crazy!

# Re: Multiply formula with multiple criteria

@Hans Vogelaar How would you adjust this if you wanted to grab the data/numbers in bold from actual cells instead so that if we ever had to change the percentages or numbers it would automatically update? Like a plug and play scenario I guess. Is that possible?

=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))

# Re: Multiply formula with multiple criteria

See the attached sample workbook. I used two cells for the 3 and 0.75, and a table for the other values.

You can edit the values, and if necessary expand the table. The formula will take the changes into account.

# Re: Multiply formula with multiple criteria

This is fantastic! Thank you so much!