Blackjack spreadsheet

I'm making a spreadsheet for tracking and counting card values for the game of Blackjack.

I have a range of cells such as (K14:R15). Some of those cells could contain any number 2 through 9. But, I also need some of those cells to possibly contain the upper case letter "A" and some of those cells to possibly contain the lower case letter "a" and some of those cells to possibly contain either the upper case letters K, Q, or J. Here is why:

If a cell in that range contains the letter K, or Q or J, I need the value of those cells to each equal 10. Of course, the Ace can either be valued as 1 or 11, so, to make the differentiation between the two, if a cell in that range has an upper case letter A then I need that cell to equal the value of 11. If a cell in that range has a lower case letter "a" then I need that cell to equal the value of 1. Then, finally, I want to sum the range K14:R15


So, for example:

....K L M N O P Q R
14 2 4 7 9 A 3 4 a
15 A K 8 J 5 a 3 Q

The sum (K14:R15) should = 99

Normally, adding the cells would be easy enough but the problem is in the letters. In the above example; the value of cells R14 and P15 need to equal 1 and the value of cells O14 and K15 need to equal 11 and the value of cells L15, N15 and R15 need to equal 10.

Is this even possible? Is there a formula for my problem or do you recommend code? Unfortunately, I know little about formulas and even less about code.


Thanks for your time,


Rob

=SUM(IF(ISNUMBER(K14:R15),K14:R15,LOOKUP(CODE(K14:R15),{65,74,75,81,97},{11,10,10,10,1})))
Array formula, use Ctrl-Shift-Enter

This uses the rank of each card if it is numeric otherwise it uses the ASCII code of the character to distinguish between "a" and "A" which is the major problem
Do not change the order of the values in the LOOKUP, the numbers have to be in ascending order

Without getting too involved in SK's formula, can you wrap it in IF(ISERROR)?
excel before 2007...
=if(iserror(SUM(IF(ISNUMBER(K14:R15),K14:R15,LOOKUP(CODE(K14:R15),{65,74,75,81,97},{11,10,10,10,1}))),"",=SUM(IF(ISNUMBER(K14:R15),K14:R15,LOOKUP(CODE(K14:R15),{65,74,75,81,97},{11,10,10,10,1})))
)

excel 2007 and later...
=IFERROR(SUM(IF(ISNUMBER(K14:R15),K14:R15,LOOKUP(CODE(K14:R15),{65,74,75,81,97},{11,10,10,10,1}))),"")

FDibbins,

Thank you for your assistance. I am using Excel 2010.

Your formula, ...excel 2007 and later...
=IFERROR(SUM(IF(ISNUMBER(K14:R15),K14:R15,LOOKUP(CODE(K14:R15),{65,74,75,81,97},{11,10,10,10,1}))),"") hides the sum if there are empty cells in my range.

To make sure I'm being clear (I don't want to waste anyone's time), Special-K's formula worked if all cells in my range are populated. If there are any empty cells, it returns #VALUE. Your formula hid the #VALUE in the sum cell when there were empty cells in my range.

Any other ideas?


Rob