## How to do tiered calculations in one formula

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:

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:

but that formula is harder for me to explain.

How to use the SUMPRODUCT function

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

SUMPRODUCT โ multiple criteria

The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to [โฆ]

SUMPRODUCT and nested IF functions

I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how [โฆ]

The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3. [โฆ]

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. [โฆ]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form