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.

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.

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug and pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

I'll try that this AM and get back with you. Thanks for the help. Perhaps I
should give you a better feeling for what I'm doing. I'm over the building
inspection department in a small city near Fort Worth TX. I use excell for
doing my monthly report for my inspectors. I have a shared folder that the
inspectors go into and log their inspections. It looks like this

A................B.............C.............D.... ......blah blah blah
1 Address Found Frame Plumbing blah blah blah
2
Under each heading there will be different letters refering to what type of
foundation it was or frame or plumbing and so on.

A...................B.............C.............D. .........blah blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so
3 4 San pier ri
5 19 Joe X
6 26 Long x to


Each day would have different letters in different rows but I still would
like to be able to calculate at the bottom of the row like in my op. There
are about 10 different inspection headings. Am I asking too much from excell?
I know it does a lot more than I am able to make it do.

Thanks for your help. I hope I have explained what I want good enough.
Wayne




"T. Valko" wrote:

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug and pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

Ok, update, the formula
=SUMPRODUCT(--(ISNUMBER(MATCH({"x";"bf";"ug";"pier"},A1:A10,0))) ,{1;6;3;4})
is the closest so far. BUT (dang it) this formula does not recognize if
there are two of the same letters in the same row.

A...................B.............C.............D. .........blah blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so x
3 4 San pier ri
5 19 Joe X
6 26 Long x to
3 1(should be 2) 6


Like under the Frame row, I might have 2,3 or even 4 framing inspections but
at different addresses. The sample above should be a total of 2 under the
Frame but it only shows 1. Is there any way for the formula to recognize
2,3,4 etc of the same letter/s?

I could do all individual formulas in a table out to the side but that would
entail a LOT of formulas! But if that is the only way...so be it! I'm lazy
and want my cake and eat it too!

Wayne

"Mule" wrote:

I'll try that this AM and get back with you. Thanks for the help. Perhaps I
should give you a better feeling for what I'm doing. I'm over the building
inspection department in a small city near Fort Worth TX. I use excell for
doing my monthly report for my inspectors. I have a shared folder that the
inspectors go into and log their inspections. It looks like this

A................B.............C.............D.... ......blah blah blah
1 Address Found Frame Plumbing blah blah blah
2
Under each heading there will be different letters refering to what type of
foundation it was or frame or plumbing and so on.

A...................B.............C.............D. .........blah blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so
3 4 San pier ri
5 19 Joe X
6 26 Long x to


Each day would have different letters in different rows but I still would
like to be able to calculate at the bottom of the row like in my op. There
are about 10 different inspection headings. Am I asking too much from excell?
I know it does a lot more than I am able to make it do.

Thanks for your help. I hope I have explained what I want good enough.
Wayne




"T. Valko" wrote:

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug and pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

OK, update! I think I've got it! I just made a row directly below the row I
am calculating with individual cell calculations and then summed the row to
where the totals needed to be! So far so good!

Thanks Bif! You got me going! Thanks!
Wayne

"Mule" wrote:

Ok, update, the formula
=SUMPRODUCT(--(ISNUMBER(MATCH({"x";"bf";"ug";"pier"},A1:A10,0))) ,{1;6;3;4})
is the closest so far. BUT (dang it) this formula does not recognize if
there are two of the same letters in the same row.

A...................B.............C.............D. .........blah blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so x
3 4 San pier ri
5 19 Joe X
6 26 Long x to
3 1(should be 2) 6


Like under the Frame row, I might have 2,3 or even 4 framing inspections but
at different addresses. The sample above should be a total of 2 under the
Frame but it only shows 1. Is there any way for the formula to recognize
2,3,4 etc of the same letter/s?

I could do all individual formulas in a table out to the side but that would
entail a LOT of formulas! But if that is the only way...so be it! I'm lazy
and want my cake and eat it too!

Wayne

"Mule" wrote:

I'll try that this AM and get back with you. Thanks for the help. Perhaps I
should give you a better feeling for what I'm doing. I'm over the building
inspection department in a small city near Fort Worth TX. I use excell for
doing my monthly report for my inspectors. I have a shared folder that the
inspectors go into and log their inspections. It looks like this

A................B.............C.............D.... ......blah blah blah
1 Address Found Frame Plumbing blah blah blah
2
Under each heading there will be different letters refering to what type of
foundation it was or frame or plumbing and so on.

A...................B.............C.............D. .........blah blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so
3 4 San pier ri
5 19 Joe X
6 26 Long x to


Each day would have different letters in different rows but I still would
like to be able to calculate at the bottom of the row like in my op. There
are about 10 different inspection headings. Am I asking too much from excell?
I know it does a lot more than I am able to make it do.

Thanks for your help. I hope I have explained what I want good enough.
Wayne




"T. Valko" wrote:

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug and pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

I think the 2nd formula I suggested will do what you want.

The easiest way to apply it would be to create a table with the letter codes
in the left column and the corresponding numeric value in the right column.

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"Mule" wrote in message
...
OK, update! I think I've got it! I just made a row directly below the row
I
am calculating with individual cell calculations and then summed the row
to
where the totals needed to be! So far so good!

Thanks Bif! You got me going! Thanks!
Wayne

"Mule" wrote:

Ok, update, the formula
=SUMPRODUCT(--(ISNUMBER(MATCH({"x";"bf";"ug";"pier"},A1:A10,0))) ,{1;6;3;4})
is the closest so far. BUT (dang it) this formula does not recognize if
there are two of the same letters in the same row.

A...................B.............C.............D. .........blah blah
blah
1 Address Found Frame Plumbing blah blah
blah
2 12 Smith so x
3 4 San pier ri
5 19 Joe X
6 26 Long x to
3 1(should be 2) 6


Like under the Frame row, I might have 2,3 or even 4 framing inspections
but
at different addresses. The sample above should be a total of 2 under the
Frame but it only shows 1. Is there any way for the formula to recognize
2,3,4 etc of the same letter/s?

I could do all individual formulas in a table out to the side but that
would
entail a LOT of formulas! But if that is the only way...so be it! I'm
lazy
and want my cake and eat it too!

Wayne

"Mule" wrote:

I'll try that this AM and get back with you. Thanks for the help.
Perhaps I
should give you a better feeling for what I'm doing. I'm over the
building
inspection department in a small city near Fort Worth TX. I use excell
for
doing my monthly report for my inspectors. I have a shared folder that
the
inspectors go into and log their inspections. It looks like this

A................B.............C.............D.... ......blah blah
blah
1 Address Found Frame Plumbing blah blah blah
2
Under each heading there will be different letters refering to what
type of
foundation it was or frame or plumbing and so on.

A...................B.............C.............D. .........blah
blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so
3 4 San pier ri
5 19 Joe X
6 26 Long x to


Each day would have different letters in different rows but I still
would
like to be able to calculate at the bottom of the row like in my op.
There
are about 10 different inspection headings. Am I asking too much from
excell?
I know it does a lot more than I am able to make it do.

Thanks for your help. I hope I have explained what I want good enough.
Wayne




"T. Valko" wrote:

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug and
pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

Got it and it workd great! Thanks so much for your help!
Now, how do I give you a rating?
You are my excell guru!
Wayne
"T. Valko" wrote:

I think the 2nd formula I suggested will do what you want.

The easiest way to apply it would be to create a table with the letter codes
in the left column and the corresponding numeric value in the right column.

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"Mule" wrote in message
...
OK, update! I think I've got it! I just made a row directly below the row
I
am calculating with individual cell calculations and then summed the row
to
where the totals needed to be! So far so good!

Thanks Bif! You got me going! Thanks!
Wayne

"Mule" wrote:

Ok, update, the formula
=SUMPRODUCT(--(ISNUMBER(MATCH({"x";"bf";"ug";"pier"},A1:A10,0))) ,{1;6;3;4})
is the closest so far. BUT (dang it) this formula does not recognize if
there are two of the same letters in the same row.

A...................B.............C.............D. .........blah blah
blah
1 Address Found Frame Plumbing blah blah
blah
2 12 Smith so x
3 4 San pier ri
5 19 Joe X
6 26 Long x to
3 1(should be 2) 6


Like under the Frame row, I might have 2,3 or even 4 framing inspections
but
at different addresses. The sample above should be a total of 2 under the
Frame but it only shows 1. Is there any way for the formula to recognize
2,3,4 etc of the same letter/s?

I could do all individual formulas in a table out to the side but that
would
entail a LOT of formulas! But if that is the only way...so be it! I'm
lazy
and want my cake and eat it too!

Wayne

"Mule" wrote:

I'll try that this AM and get back with you. Thanks for the help.
Perhaps I
should give you a better feeling for what I'm doing. I'm over the
building
inspection department in a small city near Fort Worth TX. I use excell
for
doing my monthly report for my inspectors. I have a shared folder that
the
inspectors go into and log their inspections. It looks like this

A................B.............C.............D.... ......blah blah
blah
1 Address Found Frame Plumbing blah blah blah
2
Under each heading there will be different letters refering to what
type of
foundation it was or frame or plumbing and so on.

A...................B.............C.............D. .........blah
blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so
3 4 San pier ri
5 19 Joe X
6 26 Long x to


Each day would have different letters in different rows but I still
would
like to be able to calculate at the bottom of the row like in my op.
There
are about 10 different inspection headings. Am I asking too much from
excell?
I know it does a lot more than I am able to make it do.

Thanks for your help. I hope I have explained what I want good enough.
Wayne




"T. Valko" wrote:

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug and
pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

how do I give you a rating?

Your positive response is my rating!

You're welcome and thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mule" wrote in message
...
Got it and it workd great! Thanks so much for your help!
Now, how do I give you a rating?
You are my excell guru!
Wayne
"T. Valko" wrote:

I think the 2nd formula I suggested will do what you want.

The easiest way to apply it would be to create a table with the letter
codes
in the left column and the corresponding numeric value in the right
column.

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"Mule" wrote in message
...
OK, update! I think I've got it! I just made a row directly below the
row
I
am calculating with individual cell calculations and then summed the
row
to
where the totals needed to be! So far so good!

Thanks Bif! You got me going! Thanks!
Wayne

"Mule" wrote:

Ok, update, the formula
=SUMPRODUCT(--(ISNUMBER(MATCH({"x";"bf";"ug";"pier"},A1:A10,0))) ,{1;6;3;4})
is the closest so far. BUT (dang it) this formula does not recognize
if
there are two of the same letters in the same row.

A...................B.............C.............D. .........blah blah
blah
1 Address Found Frame Plumbing blah
blah
blah
2 12 Smith so x
3 4 San pier ri
5 19 Joe X
6 26 Long x to
3 1(should be 2) 6


Like under the Frame row, I might have 2,3 or even 4 framing
inspections
but
at different addresses. The sample above should be a total of 2 under
the
Frame but it only shows 1. Is there any way for the formula to
recognize
2,3,4 etc of the same letter/s?

I could do all individual formulas in a table out to the side but that
would
entail a LOT of formulas! But if that is the only way...so be it! I'm
lazy
and want my cake and eat it too!

Wayne

"Mule" wrote:

I'll try that this AM and get back with you. Thanks for the help.
Perhaps I
should give you a better feeling for what I'm doing. I'm over the
building
inspection department in a small city near Fort Worth TX. I use
excell
for
doing my monthly report for my inspectors. I have a shared folder
that
the
inspectors go into and log their inspections. It looks like this

A................B.............C.............D.... ......blah blah
blah
1 Address Found Frame Plumbing blah blah blah
2
Under each heading there will be different letters refering to what
type of
foundation it was or frame or plumbing and so on.

A...................B.............C.............D. .........blah
blah blah
1 Address Found Frame Plumbing blah blah blah
2 12 Smith so
3 4 San pier ri
5 19 Joe X
6 26 Long x to


Each day would have different letters in different rows but I still
would
like to be able to calculate at the bottom of the row like in my op.
There
are about 10 different inspection headings. Am I asking too much
from
excell?
I know it does a lot more than I am able to make it do.

Thanks for your help. I hope I have explained what I want good
enough.
Wayne




"T. Valko" wrote:

I think I may have misunderstood what you want.

Assume A1:A10 are strings of letters. Some of which are x, bf, ug
and
pier.
There might also be multiple instances of any of these strings.

Try one of these formulas:

=SUMPRODUCT(COUNTIF(A1:A10,{"x","bf","ug","pier"}) *{1,6,3,4})

...........G..........H
1........x...........1
2........bf..........6
3........ug.........3
4........pier.......4

=SUMPRODUCT(COUNTIF(A1:A10,G1:G4)*H1:H4)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.