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:
Code:
Thresholds = Array(0, 11, 26,101)
CommRates = Array(0, 0.1, 0.15, 0.5)
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.