Rifle Club Scoring sheet - summing of numbers and letters

EDIT
Added a direct method, using sumproduct - see later ,

if you need to show transparency and the values for the shots , i would make a seperate table with the values in

AS the letter are 6 otherwise its the number in the cell
then
=IF(OR(E12="V",E12="X"),6,E12)
copy across and down
should convert into a number
then the total for the shots
Are a SUM of the cells , and then plus a countif of the X's
so
=SUM(Q12:Z12)+(COUNTIF(E12:N12,"X")/1000)

copied down

see attached

the helper table could be in a different sheet

Rather than use
=IF(OR(E12="V",E12="X"),6,E12)
a simpler formula
test to see if the cell contains a number , of it does , then use that number , otherwise it must be a letter and the number 6
=IF(ISNUMBER(E12),E12,6)

OR direct calculated total
Do the Values of X or V change
a Hardcoded formula to work out the total in 1 go would be
=SUMPRODUCT(--(NOT(ISNUMBER(E12:N12)))*6)+(COUNTIF(E12:N12,"X")/1000)+SUM(E12:N12)

Thanks Etaf, I like the idea of the second table - I was just using hidden columns in amongst the scores.
I am still hoping for a solution without a second table, but this solution will save me a lot of time.

EDIT: I just saw that ETAF also gave me a second solution without the table using SUMPRODUCT. awesome!

cheers

If your Score/Value table will never change, you can eliminate it and just use this formula directly...

Formula:

Please Login or Register  to view this content.

Alternately, this shorter array-entered** formula will also work directly without needing the Score/Value table...

Formula:

Please Login or Register  to view this content.

**Confirm this latter formula using CTRL+SHIFT+ENTER and not just Enter by itself

Rick - as the OP has 365, there is no need for array entry.

Ali - Yeah, I keep forgetting to check the OP's version (I'm working with an older version of Excel that requires array entry).

Marc - This normally entered formula (for your version of Excel) is the one you should consider...

Formula:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.