# Sum N number of values from a list, highest first.

#### SW1044

##### New Member
Hi,

I couldn't think of a clear way to title this post I'm afraid!

In this example there are several different products for sale and we record the number of each that are sold. Each product has a commission payment and the sales person receives commission for all sales EXCEPT 4 sales of the lowest ranked items.

The items are ranked with the lowest on the left. So to do this manually we start with the leftmost item and count the sales until we reach 4 sales. Then from the 5th sale onwards we multipy the sales by the commission figures and add the results up to arrive at the final commission figure.

I've given this a lot of thought and can't think of any way to achieve it without VBA. Unfortunately my Excel VBA skills are not up to the job!

I've be very grateful for any solutions or pointers thanks in advance.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Can you explain how you calculated the commission figures in your sample data please?

Can you explain how you calculated the commission figures in your sample data please?

Hi Andrew, we calculated them on paper (as decribed in para 3) and then we typed the figure in to the cell.

If this isn't clear I'll elaborate.

Cheers, Scott.

Which version of excel are you using?

I have a way to do this without VBA, which would be easier with excel 07 onwards and a couple of helper columns.

Which version of excel are you using?

I have a way to do this without VBA, which would be easier with excel 07 onwards and a couple of helper columns.

Thanks, we have Excel 2010. I've be very intested to see how it's done without VBA.

I assume that Andrew asked about the commission calculations because the results don't seem to match your description

For row 4 there's a 10 in E4 so presumably you reduce that by 4 (to 6) and then all the other figures count, so assuming commissions in row 1 you get

=(6*1)+(5*5)+(2*5)+(1*10)=51

Your figure is 75, how do you get that?

Last edited:
I assume that Andrew asked about the commission calculations because the results don't seem to match your description

For row 4 there's a 10 in E4 so presumably you reduce that by 4 (to 6) and then all the other figures count, so assuming commissions in row 1 you get

=(6*1)+(5*5)+(2*5)+(1*10)=51

Your figure is 75, how do you get that?

My sentiments exactly.

I assume that Andrew asked about the commission calculations because the results don't seem to match your description

For row 4 there's a 10 in E4 so presumably you reduce that by 4 (to 6) and then all the other figures count, so assuming commissions in row 1 you get

=(6*1)+(5*5)+(2*5)+(1*10)=51

Your figure is 75, how do you get that?

My math says:
4 or less sold is 5.00
For each item sold over 4 pieces, another 5.00 is added.

In that case they did mess up the last line, which should be 50.
(which is also why it'd be wiser for them to hard-code the formula into Excel )

This appears to have the right results, hopefully Andrew or Barry will notice any errors that I've missed.

Excel Workbook
EFGHIJKLMNOPQR
11555881010
2
3Total SoldOver 4HelperCommission
41005200011814251.00
55082100016122 59.00
60010000010 -
730320001954 30.00
8408100001394 45.00
Sheet1

My math says:
4 or less sold is 5.00
For each item sold over 4 pieces, another 5.00 is added.

In that case they did mess up the last line, which should be 50.
(which is also why it'd be wiser for them to hard-code the formula into Excel )

I don't understand that, aren't you using the commission figures in E1:L1?

This formula in P4 should give you the same results as Jason's without any helper columns

=SUM(IF(SUBTOTAL(9,OFFSET(D4,0,0,1,COLUMN(E4:L4)-COLUMN(E4)+1))< SUM(D4,4),TEXT(E4:L4+SUBTOTAL(9,OFFSET(D4,0,0,1,COLUMN(E4:L4)-COLUMN(E4)+1))-SUM(D4,4),"[<0]""0"";0"),E4:L4)*E\$1:L\$1)

confirmed with CTRL+SHIFT+ENTER and copied down

Replies
1
Views
558
Replies
0
Views
2K
Replies
6
Views
8K
Replies
1
Views
893
Replies
23
Views
5K

1,196,380
Messages
6,014,958
Members
441,857
Latest member
saraguchi2316

### 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