Excel 2007 : Sum A Column but Exclude Some Entries

@sunnybrook1, I confess I'm a little confused...

My initial reaction was that this was a tiered commission calculation, however, on further reflection I think you're saying the commission is based on cumulative sales ?

My question would thus be - if you were to assume the following values:

Please Login or Register  to view this content.

what would be the respective commission payments in K6:K9 ?

For ex.:

K7: would this be 5 * 12 * 3% or 1 * 12 * 3% ?
(ie only the 6th unit is assigned comission)

K8: would this be 10 * 10 * 4% or (9 * 10 * 3%) + (1 * 10 * 4%)

etceteras... generally a good idea to provide some examples with expected results or as Peter says better yet - post a sample file.

@sunnybrook1 - if you opt to x-post the same question on multiple boards please have the courtesy to disclose all relevant links (on each site)

http://www.mrexcel.com/forum/showthread.php?t=499724

As to the why - see: http://www.excelguru.ca/node/7

Your existing formula implies

a) max. no. of units sold on any sale to be 1
(if not then it appears commission is based on price - or is price meant to represent total sales value ?)

b) commission rate is determined by total sales over entire period

Point b) is an obvious flaw as your historic commission amounts will alter as more units are sold later on ... ie your historic values will constantly change.

Your total units should be determined by cumulative up to and including current, ie:

Please Login or Register  to view this content.

However, you've yet to answer the questions asked in the earlier post nor have you clearly outlined the expected results in your sample file based on the data within.

Until then I'm afraid IMO there's little value in people spending time creating a formula etc as this will be (in essence) based on guesstimates as to the final requirements.

Originally Posted by sunnybrook1

Our commission is based on cumulative sales but the first 4 units are not paid to the sales person unless they reach 53units sold. At that point, all 53 units are paid and are paid @ 7.5%. If they do not reach 53 units in that month, then they are paid @ 6% excluding the 1st 4 units sold.

So to be clear....

1. a "sheet" will only ever contain one months worth of sales ?

2. the commission paid is based on a flat % on the cumulative sales (less 4 units if < 53 units sold) rather than being a tiered calculation

One point remains unclear - and that relates to "Price" - does this value represent the "per unit" price or is it in fact meant to reflect "Total Sales" (based on n units) ?
If it's per unit and it's possible that > 1 unit may be sold then you're missing a column of useful information (ie total sales)

For the sake of demo. if we assume "Price" equates to Total Sales (based on n units) and that no. units can > 1 then further assuming the earlier assumptions to be accurate:

Please Login or Register  to view this content.

You don't specify a % for >= 55 units so I am assuming it is 5% payable on all items.

Please Login or Register  to view this content.

If >=55 units is not 5% add a further item to each vector in the lookup - ie add ,55 after 41 and ,0.n after 0.05 where n is % (eg 0.09 for 9%)

A revised sample file with expected results will help here.

I *think* you're saying that values for a specific Product should:

a) generate commission at a flat rate irrespective of quantity sold

and, presumably

b) units sold of the above Product should be excluded when calculating variable commission for all other products

In the sample you post it would be a good idea to intersperse sales of this "exceptional" product amongst those of the regular products so we can better follow the logic (via the expected results)