Converting specific text to numbers then sum
If you have only those 4 to convert:
A1:A10 = range where you've entered the letters.
=SUMPRODUCT(--(ISNUMBER(MATCH({"x";"bf";"ug";"pier"},A1:A10,0))) ,{1;6;3;4})
If you have a lot of coversions then you'd need to create a 2 column table
with the letters in the left column and the corresponding number value in
the right column:
...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4
Then:
=SUMPRODUCT(--(ISNUMBER(MATCH(G1:G4,A1:A10,0))),H1:H4)
--
Biff
Microsoft Excel MVP
"Mule" wrote in message
...
I would like to be able to convert specific letter/s to a specific number
and
then be able to sum the total of those numbers. Type in a letter, the
letter
remains in the cell but the value would be specific to that text.
Say you have a column with
x
bf
ug
pier
I want x to = 1
bf = 6
ug = 3
pier = 4
Then at the bottom of the column I would like to sum all of the numbers.
So it would look like
x
bf
ug
pier
14
Is there any possible way to achieve this.
|