# How to use multiple discount levels in a formula to calculate total

Occasional Contributor

# How to use multiple discount levels in a formula to calculate total

What formula would I use to calculate the Total.  The formula has to apply the discounts levels.

 Seats Price per seat Total 1,100 \$10.00 2,400 \$10.00 5,100 \$10.00 Discounts # of seats Discount % Volume Tier 1 <=1,000 0% Volume Tier 2 >1,000<=3,000 5% Volume Tier 3 >3,000<=10,000 7%
6 Replies

# Re: How to use multiple discount levels in a formula to calculate total

See screenshot below. I added a helper column that calculates the additional discount in each tier.

The formula in C2 is

``=B2*(A2-SUMPRODUCT((A2>\$B\$10:\$B\$12)*(A2-\$B\$10:\$B\$12),\$D\$10:\$D\$12))``

This can be filled down.

THANK YOU

# Re: How to use multiple discount levels in a formula to calculate total

@hanlon7054

Hans,
Using the 5100 seats, I applied your formula and got the same total you show....\$48, 530.00

But if I calculate it manually, I get a different total: \$48,730.00

# Re: How to use multiple discount levels in a formula to calculate total

Hans,
Using the 5100 seats, I applied your formula and got the same total you show....\$48, 530.00

But if I calculate it manually, I get a different total: \$48,730.00
Seats Discount % Discounted Seats Applicable Seats Price Total Price
1,000.00 0% 0 1,000.00 \$10.00 \$10,000.00
3,000.00 5% 150 2,850.00 \$10.00 \$28,500.00
1,100.00 7% 77 1,023.00 \$10.00 \$10,230.00
Total \$48,730.00

How to use multiple discount levels in a formula to calculate total

Re: How to use multiple discount levels in a formula to calculate total

Re: How to use multiple discount levels in a formula to calculate total

Re: How to use multiple discount levels in a formula to calculate total

# Re: How to use multiple discount levels in a formula to calculate total

I interpreted your original description differently. I assumed that you meant:

First 1000 seats no discount, from 1000 to 3000 seats 5% discount, all seats above 3000 seats 7% discount.