Author: Oscar Cronquist Article last updated on March 29, 2019

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This kind of formula is often used to calculate commissions, bonuses, pricing, fees or charges, discounts, volume pricing, volume rebate, and performance incentives.

The table shows the different thresholds or levels that a specific percentage applies to, if an amount is larger than the first level then multiple calculations are necessary in order to calculate the total. The formula takes care of these additional calculations as well, no need for helper-columns.

Formula in cell C10:

=SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)+ SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

There are four different values B10:B13 in order to demonstrate how the total changes based on the percentage and levels used, I will explain below how each value is calculated.

Explaining calculations

If $65 000 is used then the formula returns $2 925, here is how that number is calculated: 65 000 * 4.5% = 2 925

Amount Percentage Result
65 000 4.5% 2 925
Total 2 925

$125 000 returns $5 000. 100 000 * 4.5% = 4 500. 25 000 * 2% = 500. 4 500 + 500 = 5000.

Amount Percentage Result
100 000 4.5% 4 500
25 000 2% 500
Total 5 000

$280 000 returns $7 860. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 30 000 * 1.2% = 360. 4 500 + 3 000 + 360 = 7 860

Amount Percentage Result
100 000 4.5% 4 500
150 000 2% 3 000
30 000 1.2% 360
Total 7 860

$540 000 returns $10 580. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 250 000 * 1.2% = 3 000. 40 000 * 0.2% = 80. 4 500 + 3 000 + 3 000 + 80 = 10 580.

Amount Percentage Result
100 000 4.5% 4 500
150 000 2% 3 000
250 000 1.2% 3 000
40 000 0.2% 80
Total 10 580

Explaining formula in cell C10

The formula contains two SUMPRODUCT functions, the first one calculates the result based on the amount that is above a specific level and the corresponding percentage.

The second SUMPRODUCT function calculates tiered values based on the amounts up to the reached level and their corresponding percentages. The formula then adds those numbers and returns the total.

Step 1 - First SUMPRODUCT function

The first two logical expressions determine which tier the amount in cell B10 reaches.

(B10<$C$4:$C$7)* (B10>$B$4:$B$7)

becomes

(65000<{100000; 250000; 500000; 999999})*(65000>{0; 100000; 250000; 500000})

becomes

({TRUE; TRUE; TRUE; TRUE})*({TRUE; FALSE; FALSE; FALSE})

and returns {1; 0; 0; 0}.

Step 2 - Subtract value with tier levels

The parentheses lets you determine the order of operation, we want to subtract before we multiply the arrays in order to get the correct result we are looking for.

(B10- $B$4:$B$7)

becomes

(65000 - {0; 100000; 250000; 500000})

and returns {65000; -35000; -185000; -435000}.

Step 3 - Multiply arrays

(B10<$C$4:$C$7)*(B10>$B$4:$B$7)*(B10- $B$4:$B$7)* $D$4:$D$7

becomes

{1; 0; 0; 0}*{65000; -35000; -185000; -435000}*{0.045;0.02;0.012;0.002}

and returns {2925; 0; 0; 0}.

Step 4 - Add values

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)

becomes

SUMPRODUCT({2925;0;0;0})

and returns 2925.

Step 5 - Second SUMPRODUCT function

((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7

becomes

(65000>{100000;250000;500000;999999})*({100000;150000;250000;499999})*{0.045;0.02;0.012;0.002}

and returns {0;0;0;0}.

Step 6 - Add values in array

SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

becomes

SUMPRODUCT({0;0;0;0})

and returns 0.

Step 7 - Add numbers

SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)+ SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

becomes

2925 + 0

and returns 2925 in cell C10.

Final thoughts

The formula can be simplified to:

=SUMPRODUCT($D$4:$D$7* ((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10-$B$4:$B$7)+ ((B10>$C$4:$C$7)* ($C$4:$C$7-$B$4:$B$7))))

but that formula is harder for me to explain.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!