Converting specific text to numbers then sum
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.
|