 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007Help with summing numbers in cells that also contain text
Hello all, I have a work schedule where the column heading is the employee's name and the cells below indicate what the employee's status was for that date, as follows: col A col B Date Smith 01/01/07 8.0 SIC 01/02/07 4.0 SIC 01/03/07 D (indicating worked the day) 01/04/07 8.0 VAC TOTAL SIC Hrs: 12.0 I've tried extracting the numerical values into a hidden helper column "C" using the following formula: =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"") It extracts the correct value, however, it is formatted as text. The only way I know of to SUM column "C" is to copy and paste the values, then convert to a number. Does anyone know an easier way to do this without the copy,paste,convert, if possible? Thanks, Mike Hi Michael, The following array formula will sum your values in Column B without the need for helper cells: =SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1)))) To make this an arry formula, input it then press Ctrl-Shift-Enter. If, where you've got the 'D' response, you'd like add a specific value (eg 8hrs), change the '0' in the formula to that value. Note though that this will assume 8hrs for any blank cells in the range also. Cheers  Signaturemacropod [MVP - Microsoft Word] ------------------------- > Hello all, > [quoted text clipped - 21 lines] > > Mike Macropod, Thank you very much! That fits the bill perfectly! Regards, Mike > Hi Michael, > [quoted text clipped - 37 lines] >> >> Mike Hi Michael, Just as an addition you can also get your original formula to do the same conversion as your copy and paste step. Original formula =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"") Becomes =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")*1 or =IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"")+0 or even =--IF(RIGHT(B2,3)="SIC",LEFT(B2,3),"") All three will convert your text answer to numbers. HTH Martin > Macropod, > [quoted text clipped - 45 lines] >>> >>> Mike Hi guys, I am trying to do a SUM using this formula on a same type of column: 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml 0.1 ml But the results is 0. Do have to plug anything else in the formula? I played with it and cannot figure it out. Thanks in advance, Frank > Hi Michael, > [quoted text clipped - 65 lines] >>>> >>>> Mike You can use TEXT or format the cell with 1, 2, 3, ... decimals Using TEXT, try something like this: =TEXT( SUM( A1:A12 ), "#.####") SignatureRodrigo Ferreira Regards from Brazil > Hi guys, > [quoted text clipped - 88 lines] >>>>> >>>>> Mike Hi Frank, Apart from changing the data range to suit your data, the formula will works as posted. Cheers Signaturemacropod [MVP - Microsoft Word] ------------------------- > Hi guys, > [quoted text clipped - 88 lines] >>>>> >>>>> Mike |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.