Alternative to SUMPRODUCT?

NBVC,

I hate to even ask, but can you revise the formula with the updated reference table? I'm having trouble implementing it and referencing the correct item.

This is how I revised my formula (with the proper fields):

=SUM(MIN(CEILING(B17,1000),Calculations!A29)*Calculations!B28,MAX(0,MIN(SUM(Calculations!A28:A30),CEILING(B17,1000)-SUM(Calculations!A28:A31)))*Calculations!B29,MAX(0,MIN(SUM(Calculations!A28:A31),CEILING(B17,1000)-SUM(Calculations!A28:A31)))*Calculations!B30,MAX(0,MIN(SUM(Calculations!A28:A32),CEILING(B17,1000)-SUM(Calculations!A28:A32)))*Calculations!B31,MAX(0,MIN(SUM(Calculations!A28:A33),CEILING(B17,1000)-SUM(A28:A33)))*Calculations!B32,MAX(0,(CEILING(B17,1000)-SUM(A28:A33)))*Calculations!B33)/1000

B17 = the value (or price)

It kicks out a completely wrong #.

Hey NBVC,

Thanks again for all of your help but same issue.

The formula is performing the first calculation, but it's not adding the others.

For instance, a value of 400,000 kicks out 972.50. This takes care of the first 250,000 of the value, but we need to add that 972.50 to 150,000 x the next tier and so forth.

Does this make sense?

Also, any reason you go from 501 to 1000001 in your reference table?

I would think it should go:

0
250.01
500.01
1000.01
5000.01
15000.01

Am I wrong?