# Need help on a formula

##### New Member
I am trying to get this formula (or any ) to work on this calculation.

=SUMPRODUCT(--(SUM(\$B32:E32)>=\$B\$48:\$B\$51),SUM(E\$32:E32)-\$B\$48:\$B\$51,\$C\$48:\$C\$51-\$C\$47:\$C\$50)-SUM(B39:D39)

Here is what I am trying to do:

We have a commission structure that is set up as \$1.00 to \$225,000 = 20% \$225,001 - \$275,000 = 30% and over \$275,000 = 40%

This is of course in a tier YTD. So I am looking for a formula that can calculate the commission each month at the correct rate based on what they have done YTD.

Example:

Jan Feb Mar Apr
\$20,000 \$80,000 \$50,000 \$60,000

\$100,000 \$150,000 \$210,000 (YTD)

\$4,000 \$16,000 \$10,000 \$12,000 (Commission)

Next month will put it into the next tier

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Without understanding what data is in what cells, your formula is difficult to understand.

With these situations, I find it is best in terms of transparency and error checking to set out the tiers of the commission structure in their respective rows/columns, rather than try to accommodate an entire megaformula to deal with the three tiers in a single cell.

Consequently, set up three rows, with one row each for the three tiers. You will require IF statements in your formula.

In row 1, referring to (either the monthly sales or the YTD, depending on how you want to display it), something like:

=IF([Sales]>[Upper Bound First Tier Commission],[Upper Bound First Tier Commission]*[First Tier Commission Rate],[Sales]*[First Tier Commission Rate])

In the second row:

=IF(AND([Sales]>=]>[Lower Bound Second Tier Commission],[Sales]<=[Upper Bound Second Tier Commission]),([Upper Bound Second Tier Commission]-[Sales]*[Second Tier Commission Rate],IF([Sales]>[Upper Bound Second Tier Commission],[Upper Bound Second Tier Commission]*[Second Tier Commission Rate],0))

And finally in the third row:

=IF([Sales]>=[Third Tier Commission],([Third Tier Commission]-[Sales])*[Third Tier Commission Rate],0)

Change the names I've used in square brackets to the ranges of your particular commission levels and associated rates.

Hope that helps.

Cheers

pvr928

Replies
2
Views
80
Replies
1
Views
371
Replies
15
Views
387
Replies
6
Views
979
Replies
10
Views
125

1,203,327
Messages
6,054,751
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back