Selected Answer
I think you can replace your formula with this one, if you care to make a few preparations.
=INDEX(Prices,IF(Letters>10,2,1),Size) + (MAX(0, Letters -20) * INDEX(Prices, 3, Size))
- Create a named range by the name of "Letters", perhaps in C1 but it could be anywhere. Enter this formula in it. =LEN(SUBSTITUTE(B1," ",""))
This is because I think you don't charge for spaces. If you do, it would be just =LEN(B1)
- Add Data Validation to your cell B2. Allow only values between 3 and 6. This is so that you don't have to worry about a wrong size being specified.
- Create a named range by the name of "Size", perhaps in C2 but it could be anywhere. Enter this formula in that cell. =B2-2. This converts your 4 sizes into the numbers 1 to 4.
- Create another named range by the name of "Prices". Make it comprise the range F4:I6. Note that the 4 columns of this range are numbered 1 to 4 and the 3 rows are 1 to 3. So, you use the INDEX function to pick any of the cells in the Prices range, for example, INDEX(1, 2) would be the price for up to 10 letters in size 4.
- Rows 1 and 2 contain the prices for up to 10 and up to 20 letters. "IF(Letters>10,2,1)" will return a 1 for any number up to 10 and a 2 for any number bigger than that.
Accordingly, "INDEX(Prices, IF(Letters>10,2,1),Size)" picks the basic price from your pricelist "Prices".
- To this you wish to add any surcharge for extra letters. That is a simple multiplication of the chargeable number of letters by the price in row 3 of your pricelist. "MAX(0, Letters -20)" selects the larger of zero and Letters-20, and "INDEX(Prices, 3, Size)" picks the price per letter from the third row of Prices, depending upon size.