How to count letters as numbers in sum

[Steve Hill]

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.

 

[Paul]

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)

 

[Peo Sjoblom]

One way


=SUM(SUMPRODUCT(COUNTIF(A2:A10,"X"),10),A2:A10)

where A2:A10 is the range with numbers and X
adapt to fit accordingly by changing the ranges to your ranges, "X" and 10
should
stay where they are

 

[Steve Hill]

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).

I would be willing to post the program in its current form to a url
and link it if you are unsure about it.

Thanks.

 

[Steve Hill]

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).

I would be willing to post the program in its current form to a url
and link it if you are unsure about it.

Thanks.