Making tiers of pay based off multiple cells data

[gufemur]

As the title says, I'm trying to put a pay sheet together where there's tiers of bonus pay based off meeting a number of criteria. There are 4 categories and they are as follows,

Tier 1
20 flushes
20 alignments
55 tires
5 dealer changes

Tier 2
30 flushes
30 alignments
65 tires
5 dealer changes

Tier 3
35 flushes
35 alignments
75 tires
5 dealer changes

I want to have the numbers listed and the under the cells that have them listed, have the tier of bonus listed. I just don't know how to make a formula to tell me, based off the input numbers, whether it's tier 1, 2 or 3.

pay.jpg

Thanks

[FlameRetired]

The pattern "breaks" at cell B17. What tier do you expect 6 (for 'Home Dealer Changes')? 6 fits all tiers.

[gufemur]

The pattern "breaks" at cell B17. What tier do you expect 6 (for 'Home Dealer Changes')? 6 fits all tiers.

The numbers are basicallt stating if they hit at least those numbers, they get that tier. In other words they need to meet all the criteria for whatever tier to grt that bonus. The hime dealer changes are only required to have 5 for any tier while the other criteria gets harder as you tier up. So theres only the three tiers

[FlameRetired]

It works much better if you can offset the Tier # headers as row headers for each section. It can be done by inserting a column to the left and entering this formula in A2.

Please Login or Register  to view this content.

Then this formula in G2 and fill down.

Please Login or Register  to view this content.

[gufemur]

The only thing is that to be tier 1 all those criteria must be met and the same for the other two tiers. For example, they can have 40 flushes, 40 alignments 100 tires but if they dont have the 5 dealer changes, they get nothing. So the problem is writing one formula to assign 1 tier level based off all 4 catagories sales numbers.

[JohnTopley]

See attached.

I reformatted table (see I1:L5)

Helper in column H

=IFERROR(MATCH(F2,$J2:$L2,1),0)

in

=IF(COUNTIFS($H$2:$H$5,0),"No bonus",INDEX($J$1:$L$1,MIN($H$2:$H$5)))

[gufemur]

That worked! Thanks so much

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

And thank you for the rep.