Anna,

=SUMPRODUCT( (N5<=$A$2:$A$11)*(N5$A$1:$A$10) *
((N5-$A$1:$A$10)*($D$2:$D$11)+($F$1:$F$10)) )

Each range must have the same number of cells. Here it is 10, but you
can extend or contract the ranges, which I think is what you were
asking for.

Note:
- A1 and F1 must be 0.
- If A11 is your final sales increment, then D11 is your final %, and
F10 is the final "extra."
- The extra parentheses are important.

(If you have column headers in A1 and F1, move your data downward and
change the formula appropriately.)

Hope this helps,

- David Hilberg

On Jul 17, 7:58 am, Anna wrote:
I am trying to calculate commission amount based on different gross sales
with different %s paying on increments. Also want to be able to change gross
sales variables and %s.

Example
=IF(N5<=$A$2,N5*$D$2,IF(N5<=$A$3,(N5-$A$2)*$D$3+$F$2,IF(N5<=$A$4,(N5-$A$3)*$D$4+$F$3,IF(N5<=$A$5,(N5-$A$4)*$D$5+$F$4))))

Meaning - N5=gross sales; $A=sales increment; $D=%; $F=previous calc. added

Of course, I can only put 8 if statements in the formula. How else can this
be calculated with more variables?
--
Anna