 |
 | 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 / General Excel Questions / May 2008Summing first 2 characters of a cell (some non-numeric)
Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi there, > [quoted text clipped - 21 lines] > > Thank you! That didn't work bob it just evaluates to zero. but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help > Try this array formula > [quoted text clipped - 28 lines] > > > > Thank you! Mine was more generic, it was picking up any number before the first space. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > That didn't work bob it just evaluates to zero. > [quoted text clipped - 41 lines] >> > >> > Thank you! I know, but it didn't work when I put it in, so I changed it anyway thanks for your help I appreciated it! > Mine was more generic, it was picking up any number before the first space. > [quoted text clipped - 43 lines] > >> > > >> > Thank you! I'll bet that is because the newsreader wrapped it. This one should work, even for you =SUM(IF(ISNUMBER(--LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)))) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I know, but it didn't work when I put it in, so I changed it > [quoted text clipped - 51 lines] >> >> > >> >> > Thank you! lol @ even for me :-) Yep that worked 100% again thanks for all the help! > I'll bet that is because the newsreader wrapped it. This one should work, > even for you [quoted text clipped - 58 lines] > >> >> > > >> >> > Thank you! Glad we resolved that <go> Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > lol @ even for me :-) > [quoted text clipped - 69 lines] >> >> >> > >> >> >> > Thank you! |
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.