|
Adding multiple numbers in one cell
My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
One way...
The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
pandd15 wrote:
My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, =SUMPRODUCT(--TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)), (99*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1))+1,99))) |
Adding multiple numbers in one cell
Another option that isn't so rigid in its implementation...
Download and install the free Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm Alternate download site: http://www.download.com/Morefunc/300...-10423159.html The use this formula (entered anywhere!): =EVAL(SUBSTITUTE(E1,",","+")) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing
something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
700-2, 88-00,
900-4, 33-00, Is there a formula that will add these numbers? 87563, 8930, 98279 The "numbers" you just posted don't look anything like the the numbers you posted in your original sample! What are the dashes for? If these are the numbers: 700-2, 88-00, 900-4, 33-00, Then what results do you expect? What about that last comma? If there is not another number why is it there? -- Biff Microsoft Excel MVP "pandd15" wrote in message ... I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
My appologies for not doing a good job of explaining; these numbers are
product #'s, and vary in format, some with dashes some without, they also vary in the amount of numerals and some have letters. I am interested only in the total of numbers, as in 4 for the example below. They will always have a coma and space between the numbers. Thanks for your quick response. 700-2, 88-00, 900-4, 33-00, Is there a formula that will add these numbers? 87563, 8930, 98279 The "numbers" you just posted don't look anything like the the numbers you posted in your original sample! What are the dashes for? If these are the numbers: 700-2, 88-00, 900-4, 33-00, Then what results do you expect? What about that last comma? If there is not another number why is it there? -- Biff Microsoft Excel MVP "pandd15" wrote in message ... I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
Count the commas.
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")) pandd15 wrote: My appologies for not doing a good job of explaining; these numbers are product #'s, and vary in format, some with dashes some without, they also vary in the amount of numerals and some have letters. I am interested only in the total of numbers, as in 4 for the example below. They will always have a coma and space between the numbers. Thanks for your quick response. 700-2, 88-00, 900-4, 33-00, Is there a formula that will add these numbers? 87563, 8930, 98279 The "numbers" you just posted don't look anything like the the numbers you posted in your original sample! What are the dashes for? If these are the numbers: 700-2, 88-00, 900-4, 33-00, Then what results do you expect? What about that last comma? If there is not another number why is it there? -- Biff Microsoft Excel MVP "pandd15" wrote in message ... I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
I am interested only in the total of numbers,
as in 4 for the example below. 700-2, 88-00, 900-4, 33-00, Ok, so this leaves me even more confused! If the result you expect is 4 then you must want to COUNT the "numbers", not SUM them. If that's the case then counting the number of commas will work as long as every cell is in the same format: number, number, If you also have cells like this: number number, number Then you'll have to let us know. -- Biff Microsoft Excel MVP "Glenn" wrote in message ... Count the commas. =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) pandd15 wrote: My appologies for not doing a good job of explaining; these numbers are product #'s, and vary in format, some with dashes some without, they also vary in the amount of numerals and some have letters. I am interested only in the total of numbers, as in 4 for the example below. They will always have a coma and space between the numbers. Thanks for your quick response. 700-2, 88-00, 900-4, 33-00, Is there a formula that will add these numbers? 87563, 8930, 98279 The "numbers" you just posted don't look anything like the the numbers you posted in your original sample! What are the dashes for? If these are the numbers: 700-2, 88-00, 900-4, 33-00, Then what results do you expect? What about that last comma? If there is not another number why is it there? -- Biff Microsoft Excel MVP "pandd15" wrote in message ... I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
The counting comma's works great but unfortunately there are many numbers
that don't have comma's after the last #. "T. Valko" wrote: I am interested only in the total of numbers, as in 4 for the example below. 700-2, 88-00, 900-4, 33-00, Ok, so this leaves me even more confused! If the result you expect is 4 then you must want to COUNT the "numbers", not SUM them. If that's the case then counting the number of commas will work as long as every cell is in the same format: number, number, If you also have cells like this: number number, number Then you'll have to let us know. -- Biff Microsoft Excel MVP "Glenn" wrote in message ... Count the commas. =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) pandd15 wrote: My appologies for not doing a good job of explaining; these numbers are product #'s, and vary in format, some with dashes some without, they also vary in the amount of numerals and some have letters. I am interested only in the total of numbers, as in 4 for the example below. They will always have a coma and space between the numbers. Thanks for your quick response. 700-2, 88-00, 900-4, 33-00, Is there a formula that will add these numbers? 87563, 8930, 98279 The "numbers" you just posted don't look anything like the the numbers you posted in your original sample! What are the dashes for? If these are the numbers: 700-2, 88-00, 900-4, 33-00, Then what results do you expect? What about that last comma? If there is not another number why is it there? -- Biff Microsoft Excel MVP "pandd15" wrote in message ... I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
Let's assume that there is at least one (and maybe more) spaces between each
"number". Count them and add one. =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1 pandd15 wrote: The counting comma's works great but unfortunately there are many numbers that don't have comma's after the last #. "T. Valko" wrote: I am interested only in the total of numbers, as in 4 for the example below. 700-2, 88-00, 900-4, 33-00, Ok, so this leaves me even more confused! If the result you expect is 4 then you must want to COUNT the "numbers", not SUM them. If that's the case then counting the number of commas will work as long as every cell is in the same format: number, number, If you also have cells like this: number number, number Then you'll have to let us know. -- Biff Microsoft Excel MVP "Glenn" wrote in message ... Count the commas. =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) pandd15 wrote: My appologies for not doing a good job of explaining; these numbers are product #'s, and vary in format, some with dashes some without, they also vary in the amount of numerals and some have letters. I am interested only in the total of numbers, as in 4 for the example below. They will always have a coma and space between the numbers. Thanks for your quick response. 700-2, 88-00, 900-4, 33-00, Is there a formula that will add these numbers? 87563, 8930, 98279 The "numbers" you just posted don't look anything like the the numbers you posted in your original sample! What are the dashes for? If these are the numbers: 700-2, 88-00, 900-4, 33-00, Then what results do you expect? What about that last comma? If there is not another number why is it there? -- Biff Microsoft Excel MVP "pandd15" wrote in message ... I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing something wrong. I am new to Excel so I must be missing something. In this example what would the formula look like? The amount of numbers in C will vary in each row. a b c d 1 700-2, 88-00, 900-4, 33-00, 2 3 Thanks so much. "T. Valko" wrote: One way... The formula *must* be entered in the cell to the immediate right of the cell that you want to sum. For example: E1 = 87563, 8930, 98279 The formula *must* be entered in cell F1. Create this named formula. Select cell B1. ***this is important*** Goto the menu InsertNameDefine Name: ESum (or whatever name you want to use) Refers to: =EVALUATE(SUBSTITUTE(A1,",","+")) OK out Then: E1 = 87563, 8930, 98279 Enter this formula in **F1** : =ESum -- Biff Microsoft Excel MVP "pandd15" wrote in message ... My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
Maybe this to sum all numbers in A1 (with any non-numeric delimiter):
=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)- LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1)) "pandd15" wrote: My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1,
838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely as an example. Thanks, "Lori Miller" wrote: Maybe this to sum all numbers in A1 (with any non-numeric delimiter): =SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)- LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1)) "pandd15" wrote: My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
To count numbers contained in A1 (with comma or any other delimiter) try:
=SUM(--(FREQUENCY(COLUMN(A:IV),COLUMN(A:IV)*ISERR(-MID("."&A1&".",COLUMN(A:IV),1)))1)) "pandd15" wrote: Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1, 838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely as an example. Thanks, "Lori Miller" wrote: Maybe this to sum all numbers in A1 (with any non-numeric delimiter): =SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)- LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1)) "pandd15" wrote: My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
Should have read more carefully, if it's only comma separated, perhaps
=LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",""))+1 "pandd15" wrote: Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1, 838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely as an example. Thanks, "Lori Miller" wrote: Maybe this to sum all numbers in A1 (with any non-numeric delimiter): =SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)- LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1)) "pandd15" wrote: My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
Thanks Lori and everyone else for all the help. I have another question
about this same spreadsheet. I have dates in several columns that track how long a processes take. These dates are manually added however the process may still be incomplete thus leaving a blank in that column. These blank columns are causing my calculations to have incorrect #'s. Is there a way I can leave a cell blank without having this happen? "Lori Miller" wrote: Should have read more carefully, if it's only comma separated, perhaps =LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",""))+1 "pandd15" wrote: Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1, 838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely as an example. Thanks, "Lori Miller" wrote: Maybe this to sum all numbers in A1 (with any non-numeric delimiter): =SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)- LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1)) "pandd15" wrote: My spreadsheet contains multiple numbers in one cell they are all seperated by a coma. Is there a formula that will add these numbers? 87563, 8930, 98279 are in one cell I'd like to have the total in another colum, the total of numbers not the sum, e.g. 3 Thanks, |
Adding multiple numbers in one cell
pandd15 wrote:
Thanks Lori and everyone else for all the help. I have another question about this same spreadsheet. I have dates in several columns that track how long a processes take. These dates are manually added however the process may still be incomplete thus leaving a blank in that column. These blank columns are causing my calculations to have incorrect #'s. Is there a way I can leave a cell blank without having this happen? Probably, but without more information about your data layout and existing formulas, a specific answer is not likely. |
| All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com