Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting specific text to numbers then sum
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting text to numbers | Excel Worksheet Functions | |||
Converting numbers to text | Excel Discussion (Misc queries) | |||
Converting numbers to text | Excel Discussion (Misc queries) | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
converting numbers to text and prefill text field with 0's | Excel Discussion (Misc queries) |