"Paul" <none wrote in message ...
"Steve Hill" wrote in message
om...
I am trying to find a way in which I have a block of data containing
mostly letters between 1 and 10, but also the occasional "X" and
return the sum of the block counting the X's as 10 as well as the
orignal 10's. This would be for an archery spreadsheet and would then
be able to add up the score fully as well as identifying the X's and
10's as seperate scores of the same value automatically.

e.g. X, 10, 9, 10, 10, 8 = 57
X, X, 10, X, 9, 9 = 58

and so on.

I already have set up the columns for identifying the frequency of
each individual number and the X's, but it will not include the X's in
the score at the moment.

Thanks all.

One way (change the ranges to suit your data):
=SUM(A1:A6)+SUMPRODUCT((A1:A6="X")*10)

Thanks guys, you were both very helpful and both ways did what i
wanted, (opted for Pauls though as it was easier with the amount of
data i had as it just added what was already there with the X's).

Another query on a less important note, I was thinking about adding
some functionality into the workbook that allowed for auto calculation
of medals. At the moment each archer is assigned an id number and when
it comes to working out medals, we just use data sort for the
scores. The team medals are more difficult as we have to filter out
each university at a time and then sort the data, followed by adding
up the top four scores in the novice section and the experienced
section for each university.

Would there be some way, (I assume with macros), ,so that i could have
a seperate sheet which takes the data from the master list, containing
names, gender, scores, X's, university and experience level and
autosort them into the top three, including names and scores for each
of the below categories:

Gents Compound
Ladies Compound
Gents Experienced
Ladies Experienced
Gents Novice
Ladies Novice
Experienced Team, (top four from each university in Gents & Ladies
Experienced only, not compound)
Novice Team, (as experinced team, but for novice, obviously).

The program in its current form can be found at:
http://su.nottingham.ac.uk/archery/html/BUTTS Nottm. 03.exe

Thanks.