 |
 | 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 2007How to SUM only numbers in a range with Text?
Hello, I'm a new user to excel and forumlas. I have a column that has a total number of units in C9. In column C8 are names of people. I have to either put next to the names T 2, T 4, E 2 or E4 (F9 to AD9). I am using the following formula to subtract from the total number of units for each day in the month. Formula in Cell AE9: =(C9-(SUM(F9:AD9))/2) This works perfectly for me when I only insert number 2 or 4. In column AE,it gives me the number of UNUSED UNITs. Well now my boss wants me to enter the letter T or E with the NUMBERS, and I don't know how to adjust this forumla to IGNORE the letter so that the formula continues to work. Right now when I add a letter to the cell, the formula ignores the number. I hope this makes sense as I'm desperate for a solution. I have digged through a bunch of help sites, but I'm not technical enough to understand. Thank you for your help! ="t " &text(C9-(SUM(F9:AD9))/2,"####") > Hello, > [quoted text clipped - 18 lines] > > Thank you for your help! Hello, Thank you for your reply. When I enter this, it just puts the letter t in front of my UNUSED UNITs #, i.e. t 41. But I need to be able to enter either t or e throughout the whole row. And I don't need the letter to be in the UNUSED UNITs column, only the number. I hope this makes sense. Thanks! > ="t " &text(C9-(SUM(F9:AD9))/2,"####") > [quoted text clipped - 20 lines] > > > > Thank you for your help! you could do this the only problem i have come up with is if any of the cells f9:ad9 are blank it = an ERROR =C9-MID(F9,3,6)-MID(G9,3,6)-MID(H9,3,6)-MID(I9,3,6)-MID(J9,3,6)-MID(K9,3,6)-MID(L9,3,6)-MID(M9,3,6)-MID(N9,3,6)-MID(O9,3,6)-MID(P9,3,6)-MID(Q9,3,6)-MID(R9,3,6)-MID(S9,3,6)-MID(T9,3,6)-MID(U9,3,6)-MID(V9,3,6)-MID(V9,3,6)-MID(X9,3,6)-MID(Y9,3,6)-MID(Z9,3,6)-MID(AA9,3,6)-MID(AB9,3,6)-MID(AC9,3,6)-MID(AD9,3,6) > Hello, > [quoted text clipped - 30 lines] > > > > > > Thank you for your help! Hey, Thanks for trying. Some cells will be blank as we only enter a value when a client has been seen. F9 - AD9 are the days of the month and I enter "T 4" only when the client is seen. Thanks! > you could do this the only problem i have come up with is if any of the cells > f9:ad9 are blank it = an ERROR [quoted text clipped - 35 lines] > > > > > > > > Thank you for your help! Aundria, If you always have just one letter, then array enter (enter using Ctrl-Shift-Enter) this formula =(C9-SUM(IF(F9:AD9<>"",VALUE(MID(F9:AD9,2,LEN(F9:AD9))))))/2 HTH, Bernie MS Excel MVP > Hello, > [quoted text clipped - 18 lines] > > Thank you for your help! Bernie, I'm not sure if I'm doing the CTRL+SHIFT+ENTER right. Can you dumb it down more for me? When I try this, nothing gets entered into the cell. Right now I do a COPY/PASTE of the formula into the CELL where UNUSED UNITs are calculated. I see "####" indicating there is a error somewhere. It will always be 1 letter and 1 number, i.e. T 4 OR E 2. There is a space between the letter and the number. Thank you for your help! > Aundria, > [quoted text clipped - 28 lines] > > > > Thank you for your help! Bernie, I hope you wont give up! I liked your formula and so I wanted to add that when I click the "!" to see about the error, it looks to me that it gets to 46/VALUE and breaks. What does VALUE do? That seems to be the problem... When I do EVALUATE FORMULA, it underlines and italicizes (#VALUE!<>"" Then I click EVALUATE again and it underlines/italicizes (IF( #VALUE!,VALUE(MID(F9:AD9,2,LEN(F9:AD9)))) I hit EVALUATE again (46-#VALUE!)/2 I hit again ( #VALUE!)/2 I hit again #VALUE!/2 I hit it for the last time and I only can RESTART #VALUE! Thanks for your help! > Aundria, > [quoted text clipped - 28 lines] > > > > Thank you for your help! Again, using Ctrl-Shift-Enter: =(C9-SUM(IF(F9:AD9<>"",VALUE(MID(F9:AD9,3,LEN(F9:AD9))))))/2 Since you have a letter and a space, the text string is really two characters long, so I changed the ,2, to ,3, in the MID function. =(C9-SUM(IF(F9:AD9<>"",VALUE(MID(F9:AD9,3,1)))))/2 would also work, since you only have a 2 or a 4 in the cell (a number string one character long). HTH, Bernie MS Excel MVP > Bernie, > [quoted text clipped - 68 lines] >> > >> > Thank you for your help! Bernie, I found out that the "array enter a forumla" just puts the squirly brackets around the formula. So when I do this, I still get the #VALUE! error. Any ideas why? I've looked at the Excel HELP and I've tried to rectify this error. If you don't know why i'm getting the error, please just let me know so I'll quit begging :) Thanks! > Again, using Ctrl-Shift-Enter: > [quoted text clipped - 84 lines] > >> > > >> > Thank you for your help! Bernie, I'm not sure why Sandy's worked and yours did not, but I'm thankful to you for getting me on the right track! Aundria > Again, using Ctrl-Shift-Enter: > [quoted text clipped - 84 lines] > >> > > >> > Thank you for your help! Tell him he is an idiot! Use an extra column for any text entries  SignatureRegards, Peo Sjoblom > Hello, > [quoted text clipped - 21 lines] > > Thank you for your help! Peo, nice joke ! I suggest that its better to *firstly* persuade her boss to use the extra column, otherwise if *the bossy symptoms persist*, i suggest that she ask her boss to do it himself. we're excellers to reduce idiots <g>! > Tell him he is an idiot! Use an extra column for any text entries > [quoted text clipped - 23 lines] > > > > Thank you for your help! One option is to produce a set of helper columns, so from "T 2" in F9 you could extract the 2 by =RIGHT(F9), and copy across appropriately. Use the new columns in your SUM (and hide the new columns if you want to). SignatureDavid Biddulph > Hello, > [quoted text clipped - 21 lines] > > Thank you for your help! David, I did what you suggested. I started with F42 to AD42, which corresponds with F9 to AD9. I have only numbers in my cell on the second page. I then put the below formula in AE9 (UNUSED UNITS column) but now the value is always the same as in C9 (TOTAL UNITS). It's like the formula is no long doing the SUM / 2 part. =(C9-(SUM(F42:AD42))/2) What gives? Thanks! > One option is to produce a set of helper columns, so from "T 2" in F9 you > could extract the 2 by =RIGHT(F9), and copy across appropriately. Use the [quoted text clipped - 24 lines] > > > > Thank you for your help! Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter produces text, rather than a number. I apologise for causing you confusion. SignatureDavid Biddulph > David, > [quoted text clipped - 44 lines] >> > >> > Thank you for your help! David, Thanks for trying. This forumla only works if every sell in the range of F9:AD9 has something in it. Some cells are empty and so I get a value error. This works if every cell is populated but we only see the client 1-3 times a week. Any other ideas? Thanks! > Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter > produces text, rather than a number. I apologise for causing you confusion. [quoted text clipped - 46 lines] > >> > > >> > Thank you for your help! You could solve that by replacing the =--RIGHT(F9) formula by =IF(F9="","",--RIGHT(F9)) SignatureDavid Biddulph > David, > [quoted text clipped - 7 lines] > > Any other ideas? >> Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter >> produces text, rather than a number. I apologise for causing you [quoted text clipped - 58 lines] >> >> > >> >> > Thank you for your help! Hi Aundria, Try this formula in AE9 =(C9-(SUM(RIGHT(F9:AD9)*1)/2)) This is an array formula and must be committed with Ctrl+Shift+Enter and not just enter HTH Martin > Hello, > [quoted text clipped - 21 lines] > > Thank you for your help! Sorry that doesn't handle blank cells, although it will work if you put a zero in the blank cells. Can somebody else help out here? I think an ISTEXT or maybe an IF(ISTEXT can be worked into this array formula to handle the blanks but I can't seem to get the syntax right. The array formula I started with is =(C9-(SUM(RIGHT(F9:AD9)*1)/2)) Regards Martin > Hi Aundria, > [quoted text clipped - 33 lines] >> >> Thank you for your help! > Can somebody else help out here? As an academic exercise - (I think that Bernie's formulas is the way to go) - this should handle blank cells or cells returning an empty string: =(C9-(SUM(IF(F9:AD9<>"",RIGHT(F9:AD9)*1))/2)) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Sorry that doesn't handle blank cells, although it will work if > you put a zero in the blank cells. [quoted text clipped - 47 lines] >>> >>> Thank you for your help! Thanks Sandy, That's cleared up the blanks issue very well. I am intrigued as to why you prefer the VALUE(MID approach to the RIGHT approach. VALUE(MID requires 3 characters in the input cells i.e Tspace4 RIGHT will handle Tspace 4, T4 or 4 or even a typo like Tspacespace4 Or am I missing something else again? Regards Martin >> Can somebody else help out here? > [quoted text clipped - 55 lines] >>>> >>>> Thank you for your help! Hi Martin, Bernie's formula will take care of double, (or more), digit numbers after the text ot letter and a space, your will only capture the final digit even if there are more. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thanks Sandy, > [quoted text clipped - 74 lines] >>>>> >>>>> Thank you for your help! OK, Thanks again Sandy. > Hi Martin, > [quoted text clipped - 82 lines] >>>>>> >>>>>> Thank you for your help! Hi Martin, Seeing my response in print it looks a bit terse - I must have been associating with Harlan too much <g> I did not mean to put your formula down, I was just in a bit of a rush when I wrote it. Your formula does satisfy the OP's requirements as stated with no double digit numbers given. My apologies, it was not meant that way. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > OK, Thanks again Sandy. > Hi Martin, > [quoted text clipped - 6 lines] > > My apologies, it was not meant that way. Aren't you supposed to be a "dour" Scot? <vbg> SignatureRegards, Peo Sjoblom > Aren't you supposed to be a "dour" Scot? <vbg> LOL Incidentally does anyone know what has happened to Harlan lately? SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >> Aren't you supposed to be a "dour" Scot? <vbg> > > LOL > > Incidentally does anyone know what has happened to Harlan lately? Vacation? Or maybe there is a convention for mathematically inclined curmudgeons somewhere? He was gone for some time and returned with a few posts but he seems to be gone again, we have a saying in Sweden "You don't miss the sow until the sty is empty" Somehow he keeps other posters in line or they will be corrected <vbg> SignatureRegards, Peo Sjoblom =(C9-SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(F9:AD9,"T ",""),"E ",""))))/2 will work as well SignatureRegards, Peo Sjoblom > Hi Martin, > [quoted text clipped - 82 lines] >>>>>> >>>>>> Thank you for your help! Sandy, You did it!! Thanks everyone for your help, especially you Sandy! Best Regards, Aundria > > Can somebody else help out here? > [quoted text clipped - 54 lines] > >>> > >>> Thank you for your help! Thanks for the thanks but it was Martin W's formula How about that Martin? You won the race <g> SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Sandy, > [quoted text clipped - 69 lines] >> >>> >> >>> Thank you for your help! Hi Sandy, It's not about the race It's about the journey <g> I got as much from this thread as the OP. P.S. Your earlier thread didn't sound terse or Harlanesque to me. I'm an Aussie Sandy, you need to be trying very hard to ruffle my feathers. Thanks Again Martin > Thanks for the thanks but it was Martin W's formula > [quoted text clipped - 74 lines] >>> >>> >>> >>> Thank you for your help! Sandy, Oh I was confused! Thanks Martin!!! I went back and put a YES for his POST also. I hope this is what gives everyone a good rating. You all are very helpful! Aundria > Thanks for the thanks but it was Martin W's formula > [quoted text clipped - 73 lines] > >> >>> > >> >>> Thank you for your help! |
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.