Hi everyone. I hope you can help me.

I have a number of columns with sales revenue in each column. Column C has values of sales which a percentage is paid as commission and I have used the following formula to count the number of sales in this column;

=COUNTIF(C2:C33,"<>"&"")

The first 10 sales are calculated at 10%, the second are at 15% and all sales greater than 21 are calculated at 20%. However, not all sales include ITEM C, so I require a formula please that can do the following;

IF(C34<11, then sum of the first 10 entries that are greater than 0) and multiply by 10%, IF(C34 is between 11 and 21, then sum the first 10 entries that are greater than zero and multiply by 10% plus sum the second 10 entries that are greater than zero and multiply by 15%, IF (C34 >20, then sum the first 10 entries that are greater than zero and multiply by 10% plus sum the second 10 entries that are greater than zero and multiply by 15% and sum the rest of the entries that are greater than zero and multiply by 20%.

Sorry if I haven't explained it better, but my brain is really fried with this one.


Example.xlsx

Attached Images

Capture.JPG (52.4 KB, 30 views)