SUMPRODUCT incorrectly calculating price tiers

I’m trying to figure out how to calculate the total amount a customer will pay for products that are discounted in tranches. My problem is that using SUMPRODUCT appears to count each tranche multiple times, as illustrated below.

Here’s the pricing sheet. The idea is that 1-10 seats costs $100/seat; the next 10 seats (from 11-20) cost $75/seat; the next 10 (from 21-30) costs $50/seat; and 31 seats and up costs $25/seat.

Base price: $100

Please Login or Register  to view this content.

I’ve entered this into Excel as follows:

Please Login or Register  to view this content.

The formula I used is: =SUMPRODUCT(--(Seat Count>Threshold), Seat Count-Threshold, EffectiveRate). Below is a table comparing the actual prices with the incorrectly-calculated price via .

Please Login or Register  to view this content.

Breaking the formula down, it becomes clear that it counts each tranche multiple times. E.g., for 11 users, it counts (11*100) + (1*75). For 20 users it counts (20*100)+(10*75).

Please Login or Register  to view this content.

Any advice on how to fix this would be greatly appreciated.

Originally Posted by awenner

Please Login or Register  to view this content.

The formula I used is:
=SUMPRODUCT(--(Seat Count>Threshold), Seat Count-Threshold, EffectiveRate).

In order for the formula to work, what you call "effective rate" should be a differential rate. If the "discount rate" is column C and 0% is in row 2, enter the following formulas:

D2: =C2
D3: =C3-C2
Copy D3 into D4:D5

That solved the problem. Thanks!

Originally Posted by awenner

Please Login or Register  to view this content.

Originally Posted by joeu2004

In order for the formula to work, what you call "effective rate" should be a differential rate. If the "discount rate" is column C and 0% is in row 2, enter the following formulas:
D2: =C2
D3: =C3-C2
Copy D3 into D4:D5

Originally Posted by awenner

That solved the problem.

But not as I wrote it. If you got it to work, presumably you fixed my mistakes. Good for you!

For posterity, I think the correct solution is....

Add a "differential price" column to the right of "effective rate" [sic]. (I would call it "effective price".) If "effective rate" is in column D and 100 is in row 2, enter the following:

E2: =D2
E3: =D3-D2
Copy E3 into E4:E5

Then the formula to calculate total price is:

=SUMPRODUCT(--(seatCount>$B$2:$B$5), seatCount-$B$2:$B$5, $E$2:$E$5)