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, 28 views)

I too found it difficult to get a worksheet formula that was easy to maintain and in the end gave up and wrote a user-defined function to do it instead.

In the attached, I've worked out what I think the answer should be in cell E34 as a cross-check.
You use the function as follows, (see cell F34):
=Commission(C2:C33)

It counts numbers from the top (if there's a zero it calls that a sale for counting purposes, but blanks and text are not counted).
You can use it in a different way, to include multiple non-contiguous areas like:
=Commission((C2:C9,C12:C16,C19:C27,C29:C31))


Two things to note here:

1. You need the double parentheses
2. The order the cells are treated in is the order in which they appear in the inner parentheses. What's more, if any of the areas has a multiple columns the numbers are processed by running down the first column, then the second etc.

To show you the importance of these 2 points see cells H34:I34 which process the same cells but return different values. Also see M9:M11.

There's a comment in the code for the function syaing you can add more threholds, for example you could change it to:
which would mean zero commision until 10 sales, 10% commission for the 11th to 25th sales, 15% commission for the 26th to the 100th sales, and 50% commision for sales 100 and above.




As an aside, your formula for counting sales in cell C34 is dangerous because it counts cells containing anything at all, including text. I put text in cell C3 to demonstrate and a simpler and more reliable formula for this in cell C35.
All academic since it's not used to calculate the commission.

Attached Files

msOfficeForums43080Example.xlsm (17.1 KB, 5 views)

Very nice, p45cal My first thought—before seeing your answer—was a conditional formula in Col D which would:
Count the number of numerical entries between the adjoining Col C cell and C2;
Multiply the Col C value by .1, .15 or .2 accordingly.

Won't that work, and be easy enough?

Yes, it will work, and it's straightforward and quite easy to maintain. It does require at least one helper column, which can be hidden.
I thought the OP was looking for a single-cell formula.

As usual, the people on this forum are amazing and absolute geniuses. Thank you for your help. It has been a few years since I have asked for help.

In an ever- changing self focused world, its really nice to see that the qualities of the people in this forum has not changed.

Thank you again