Author: Oscar Cronquist Article last updated on June 02, 2022 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.

I have written an article about how to Use price ranges to determine discounts, it also explains how to calculate linear discounts.

## 1. Formula 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.

## 2. Explaining calculations

### 2.1 Example 1 - \$65 000 If \$65 000 is used then the formula returns \$2 925, here is how that number is calculated: 65 000 * 4.5% = 2 925

The amount is in cell B10 and the calculation is in cell C10, see the image above.

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

### 2.2 Example 2 - \$125 000 \$125 000 returns \$5 000. 100 000 * 4.5% = 4 500. 25 000 * 2% = 500. 4 500 + 500 = 5 000.

The amount is in cell B11 and the calculation is in cell C11, see the image above.

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

### 2.3 Example 3 - \$280 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

The amount is in cell B12 and the calculation is in cell C12, see the image above.

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

### 2.4 Example 4 - \$540 000 \$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.

The amount is in cell B13 and the calculation is in cell C13, see the image above.

 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

## 3. 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.

Recommended article: How to return a value if lookup value is in a range

#### 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 let you control 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.

## 4. 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.

Excel 365 users can use this smaller dynamic array formula:

=LET(v,\$B\$4:\$B\$7,x,\$C\$4:\$C\$7,y,B10,SUMPRODUCT(\$D\$4:\$D\$7*((y<=x)*(y>v)*(y-v)+((y>x)*(x-v))))) 