Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers and text
***** I am using Excel 2003 and Excel 2007 *****
I have an array that contains many entries like this: U4 U4.6 U1 P8 P2 V9 V3 V3.5 and others. The number is always preceeded by one consonant (one text letter from A - Z). I want to add all the U numbers together for a total (I do not want to count how many times they occur, I need total the numbers after the U or other single text character). I want the result to be listed in the formula cell. In the above group of numbers the total value of U should be "9.6". I would also like to do the same with any of the text letters in the cells -- P, V, etc. I am thinking and trying something like these: =SUM(IF(C38:N65="U",C38:N65)) =SUM(IF(C38:N47,"U",N(C38:N47))) but get "#VALUE!" errors. Basically, I want to strip the text character out and SUM the number. Any help will be appreciate no matter how difficult the algorithm or formula is. Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers and text
Try this array formula** :
Assumes that if a cell contains a letter it also contains a number. =SUM(IF(LEFT(A1:A8)="u",--MID(A1:A8,2,10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Bluesquid619B" wrote in message ... ***** I am using Excel 2003 and Excel 2007 ***** I have an array that contains many entries like this: U4 U4.6 U1 P8 P2 V9 V3 V3.5 and others. The number is always preceeded by one consonant (one text letter from A - Z). I want to add all the U numbers together for a total (I do not want to count how many times they occur, I need total the numbers after the U or other single text character). I want the result to be listed in the formula cell. In the above group of numbers the total value of U should be "9.6". I would also like to do the same with any of the text letters in the cells -- P, V, etc. I am thinking and trying something like these: =SUM(IF(C38:N65="U",C38:N65)) =SUM(IF(C38:N47,"U",N(C38:N47))) but get "#VALUE!" errors. Basically, I want to strip the text character out and SUM the number. Any help will be appreciate no matter how difficult the algorithm or formula is. Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers and text
On Fri, 22 May 2009 14:16:00 -0700, Bluesquid619B
wrote: ***** I am using Excel 2003 and Excel 2007 ***** I have an array that contains many entries like this: U4 U4.6 U1 P8 P2 V9 V3 V3.5 and others. The number is always preceeded by one consonant (one text letter from A - Z). I want to add all the U numbers together for a total (I do not want to count how many times they occur, I need total the numbers after the U or other single text character). I want the result to be listed in the formula cell. In the above group of numbers the total value of U should be "9.6". I would also like to do the same with any of the text letters in the cells -- P, V, etc. I am thinking and trying something like these: =SUM(IF(C38:N65="U",C38:N65)) =SUM(IF(C38:N47,"U",N(C38:N47))) but get "#VALUE!" errors. Basically, I want to strip the text character out and SUM the number. Any help will be appreciate no matter how difficult the algorithm or formula is. Thank you This formula must be **array-entered**: =SUM(IF(LEFT(A1:A8,1)="U",--MID(A1:A8,2,15),0)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers and text
On May 22, 2:46*pm, "T. Valko" wrote:
Try this array formula** : Assumes that if a cell contains a letter it also contains a number. =SUM(IF(LEFT(A1:A8)="u",--MID(A1:A8,2,10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Bluesquid619B" wrote in message ... ***** * *I am using Excel 2003 and Excel 2007 * ***** I have an array that contains many entries like this: U4 U4.6 U1 P8 P2 V9 V3 V3.5 and others. The number is always preceeded by one consonant (one text letter from A - Z). I want to add all the U numbers together for a total (I do not want to count how many times they occur, I need total the numbers after the U or other single text character). I want the result to be listed in the formula cell. *In the above group of numbers the total value of U should be "9.6". I would also like to do the same with any of the text letters in the cells -- P, V, etc. I am thinking and trying something like these: =SUM(IF(C38:N65="U",C38:N65)) =SUM(IF(C38:N47,"U",N(C38:N47))) but get "#VALUE!" errors. Basically, I want to strip the text character out and SUM the number. Any help will be appreciate no matter how difficult the algorithm or formula is. Thank you- Hide quoted text - - Show quoted text - What if the list is $10 for account "equipment" and then the next line is $22 for account "office supplies" - how do you tell "if" to total on another line all the "equipment" totals separate from the "office supplies", etc.? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers and text
Show us some samples of your data and the desired result.
-- Biff Microsoft Excel MVP "Terry Ciraulo" wrote in message ... On May 22, 2:46 pm, "T. Valko" wrote: Try this array formula** : Assumes that if a cell contains a letter it also contains a number. =SUM(IF(LEFT(A1:A8)="u",--MID(A1:A8,2,10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Bluesquid619B" wrote in message ... ***** I am using Excel 2003 and Excel 2007 ***** I have an array that contains many entries like this: U4 U4.6 U1 P8 P2 V9 V3 V3.5 and others. The number is always preceeded by one consonant (one text letter from A - Z). I want to add all the U numbers together for a total (I do not want to count how many times they occur, I need total the numbers after the U or other single text character). I want the result to be listed in the formula cell. In the above group of numbers the total value of U should be "9.6". I would also like to do the same with any of the text letters in the cells -- P, V, etc. I am thinking and trying something like these: =SUM(IF(C38:N65="U",C38:N65)) =SUM(IF(C38:N47,"U",N(C38:N47))) but get "#VALUE!" errors. Basically, I want to strip the text character out and SUM the number. Any help will be appreciate no matter how difficult the algorithm or formula is. Thank you- Hide quoted text - - Show quoted text - What if the list is $10 for account "equipment" and then the next line is $22 for account "office supplies" - how do you tell "if" to total on another line all the "equipment" totals separate from the "office supplies", etc.? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing text and then adding numbers if the text matches | Excel Worksheet Functions | |||
Adding Text, Date and Numbers together in a formula | Excel Discussion (Misc queries) | |||
Adding numbers to text! | Excel Worksheet Functions | |||
adding cells with text and numbers | Excel Discussion (Misc queries) | |||
Adding cells with numbers and text | Excel Worksheet Functions |