Multiple IF/AND Formula

[LisaMarie1981]

Hi there,

I am trying to do a formula based on a tiered system. So an employee gets paid out per deal based on how many deals they get. See example below. So for February they would get paid out $300*4=$1,200 based on hitting tier 2 for 3 or more deals but less than tier 3 for DCI Deals and $400*10=$4,000 for Certainli Deals for hitting tier 3, 10 deals or higher giving them a total of $5,200 bonus that month. Any help is appreciated, thank you so much!!

	Tier 1	Tier 2	Tier 3
Company A	$      150.00	$      300.00	$      600.00
Company B	$      100.00	$      200.00	$      400.00

	Tier 1	Tier 2	Tier 3
Company A	0	3	6
Company B	0	5	10

	January	February	March	April	May	June	July	August
A Deals	3	4	6	4	2	3	1	3
B Deals	5	10	5	2	3	5	2	1

[#2]

@LisaMarie1981 

How is your spreadsheet actually arranged? In particular the data pertaining to the deals that any given employee has achieved in any given month.

The array in your query doesn't seem likely (for one thing, no employee is identified); yet it's the way the data are arrayed that influences the formula.

Is it possible for you to give us an actual workbook that is arranged as your actual workbook is (a mockup is fine, so long as it's an accurate representation, without real names; even without real dollar figures if those are confidential). You've posted a lot of questions over recent months, so it's quite possible that you can simply attach a sample workbook to your message. Otherwise, use OneDrive or GoogleDrive and paste a link here that grants access.

[#3]

@mathetes see attached. I just want to be able to plug and play with the number of deals in rows 2 & 3 and change the dollar values for each tier in rows 8 & 9 or the number of deals required for each tier in rows 12 & 13 and then have them be able to see what they could potentially make each month in row 5.

[#4]

@LisaMarie1981 

I changed your tables into a single table (well, actually two tables put together, but one for the DCI values, the other for the Certainli. Then there's one formula, a long one, using the LET function.

=LET(DBonLvl,INDEX($B$9:$D$9,,MATCH(B$2,$B$8:$D$8,1)),CBonLvl,INDEX($B$11:$D$11,,MATCH(B$3,$B$10:$D$10,1)),(DBonLvl*B2)+(CBonLvl*B3))

I've attached the sheet, but here's what it looks like now.

If you're not familiar with that, here's a reference. And if you'd like I can spell out how this one works. Let me know if that'd be helpful.

I'm sure, as we've talked about in earlier exchanges, there are different ways to do this. One of the delightful features of Excel.

[#5]

@LisaMarie1981 

By the way, there's such a big jump in the bonus when, for example, a person goes from Tier 2 to Tier 3 in the Certainli category. Going from 9 to 10 is great for the person who hits 10, really tough on the person who worked just as hard, maybe harder, but only made 9.

You might want to consider adopting a system that works something like the US Federal Tax system, where an additional dollar income, putting you "from one bracket into the next up" actually only costs the person that higher rate for that last dollar. Granted, it's the reverse of your situation in some ways, since the bonus system is paying out whereas the tax system is asking you to pay. But that graduated tax table is a good concept.

I've attached a calculator for Federal Taxes as an example--- this is more complicated, since it handles multiple years as well as multiple levels of income. But it illustrates my point about moving from one bracket to another. 

[#6]

OK, Thank you so very much for this. This is above and beyond! Much appreciated