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.