Converting specific text to numbers then sum

[Mule]

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.

 

[T. Valko]

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)

 

[T. Valko]

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)

 

[Mule]

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

 

[Mule]

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]

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

 

[T. Valko]

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)

 

[Mule]

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]

how do I give you a rating?

Your positive response is my rating!

You're welcome and thanks for the feedback!