| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
I want to be able to add a column with mixed numerals. For example: The sum of A2:A34 with the values in the cells being 1B or 1F or so.  What would the formula be? -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will remove all alpha-characters from cells, leaving only the "number" parts....... hth Vaya con Dios, Chuck, CABGx3 "excel303" wrote: I want to be able to add a column with mixed numerals. For example: The sum of A2:A34 with the values in the cells being 1B or 1F or so. What would the formula be? -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
That works, but, I need to keep the values in the cells, only add the numbers. ASAP Utilities is awesome by the way. -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Copy your data over to a helper column and run the routine on that
column.....this will keep your original data intact........ Vaya con Dios, Chuck, CABGx3 "excel303" wrote: That works, but, I need to keep the values in the cells, only add the numbers. ASAP Utilities is awesome by the way. -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
I'm using the following formula with a helper column, now I'm not able to add the helper column. =LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"01 23456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)))) -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
I don't understand why you are using the formula.........I thought you just
had mixed text and numerical values in column A and wanted to just add the numerical parts...............be that the case, then just copy and paste-special-values your column A over to a helper column and then use ASAP Utilities to delete all the alpha-characters in that helper column then add the numbers that are left..... Vaya con Dios, Chuck, CABGx3 "excel303" wrote: I'm using the following formula with a helper column, now I'm not able to add the helper column. =LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"01 23456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)))) -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
I apologize for the confusion, I'm creating a worksheet for other users to input. I have a column with mixed numerals: ie 1b or 1f or 2b....Ijust need to add the numbers automaticaly, but still keep the letters present. Thank you so much for your help;) CLR Wrote: I don't understand why you are using the formula.........I thought you just had mixed text and numerical values in column A and wanted to just add the numerical parts...............be that the case, then just copy and paste-special-values your column A over to a helper column and then use ASAP Utilities to delete all the alpha-characters in that helper column then add the numbers that are left..... Vaya con Dios, Chuck, CABGx3 -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Are you wanting to "count" the 1b's and the 1f's and the 2b's, etc?
Then try =COUNTIF(A:A,"1b") =COUNTIF(A:A,"1f") =COUNTIF(A:A,"2b") If you are wanting to sum the 1's and 2's, then try my suggestion in my last post. Post back if you're still having trouble.......maybe give some specific cell values and what results you expect. Vaya con Dios, Chuck, CABGx3 "excel303" wrote: I apologize for the confusion, I'm creating a worksheet for other users to input. I have a column with mixed numerals: ie 1b or 1f or 2b....Ijust need to add the numbers automaticaly, but still keep the letters present. Thank you so much for your help;) CLR Wrote: I don't understand why you are using the formula.........I thought you just had mixed text and numerical values in column A and wanted to just add the numerical parts...............be that the case, then just copy and paste-special-values your column A over to a helper column and then use ASAP Utilities to delete all the alpha-characters in that helper column then add the numbers that are left..... Vaya con Dios, Chuck, CABGx3 -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
Ok, my spreadsheet is used to track different types of information, I'm good with the rest of it. The one column I need help with is from U4:U27. in this column the user will input a letter(F or B) followed by the quantity of each. Is there a way to sum the numbers only and still have the original value showing in the cell the user typed in? 1F 1b Total: 2 -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
If you want to use a helper column, then you can change each value into a
number and sum that column using this...copied down from U4 to U27 =IF(LEN(U4)0,MID(U4,2,99)*1,"") The only way I know to sum them without using a helper column would be a humongus formula, like this...... =SUM(MID(U4,2,99*1),MID(U5,2,99)*1,MID(U6,2,99)*1. ....etc etc to U27) hth Vaya con Dios, Chuck, CABGx3 "excel303" wrote in message ... Ok, my spreadsheet is used to track different types of information, I'm good with the rest of it. The one column I need help with is from U4:U27. in this column the user will input a letter(F or B) followed by the quantity of each. Is there a way to sum the numbers only and still have the original value showing in the cell the user typed in? 1F 1b Total: 2 -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
you are awesome!!!!!!!!!:) :) :) :) THANK YOU, THANK YOU, THANK YOU -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula help
You're very welcome, and thanks for the thanks.....
Vaya con Dios, Chuck, CABGx3 "excel303" wrote in message ... you are awesome!!!!!!!!!:) :) :) :) THANK YOU, THANK YOU, THANK YOU -- excel303 ------------------------------------------------------------------------ excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816 View this thread: http://www.excelforum.com/showthread...hreadid=515440 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| 2 Nesting questions | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
