 |
 | 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 2007Left + Sum
I have the following in A1 $2 Win $2 Win $2 Win How do I total just the dollar amounts in cell B2? I thought it was the left function, but I can't figure it out. Thanks If your source values are in A1:A10... Try this: =SUMPRODUCT(--(LEFT(A1:A10&" ",SEARCH(" ",A1:A10&"0 ")-1))) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) >I have the following in A1 > [quoted text clipped - 6 lines] > > Thanks I think you left out a "0" in the first concatenation.... =SUMPRODUCT(--(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1))) (I would have used FIND instead of SEARCH for this myself... it's shorter to type.<g>) Rick > If your source values are in A1:A10... > Try this: [quoted text clipped - 19 lines] >> >> Thanks I could be wrong - I was known to wrong once before<g> - but I needed a SUBSTITUTE() function to get rid of the dollar sign as well: =SUMPRODUCT(--(SUBSTITUTE(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1),"$","")))  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 >I think you left out a "0" in the first concatenation.... > [quoted text clipped - 28 lines] >>> >>> Thanks The formula Ron posted (with the addition of the missing "0") worked fine for me without having to handle the dollar sign separately (no matter what formatting I gave to the cells in column A). Are you saying the formula didn't work for you as written? Rick >I could be wrong - I was known to wrong once before<g> - but I needed a >SUBSTITUTE() function to get rid of the dollar sign as well: [quoted text clipped - 34 lines] >>>> >>>> Thanks Hi Rick, Yes that's right, when I copied the three values from the post into a spreadsheet I ended up with three cells formatted as General but containing text. When I used your formula, hilighted just LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1) and pressed F9 I get {"$2";"$2";"$2";"0";"0";"0";"0";"0";"0";"0"}returned. Including the next set of parenthesis and the double unary I get {#VALUE!;#VALUE!;#VALUE!;0;0;0;0;0;0;0}returned. I assume that you get something different? 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 > The formula Ron posted (with the addition of the missing "0") worked fine > for me without having to handle the dollar sign separately (no matter what [quoted text clipped - 41 lines] >>>>> >>>>> Thanks When I examine the Left function results, I get 2 without the dollar sign. I just tried several reformats and re-entries of the $2 Win text (including with a leading apostrophe) and the dollar sign always gets removed and the SUMPRODUCT always produces the correct summation. Rick > Hi Rick, > [quoted text clipped - 55 lines] >>>>>> >>>>>> Thanks Hi Rick, Very curious. When I examine just: A1:A10&"0 " I get: {"$2 Win0 ";"$2 Win0 ";"$2 Win0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 "} If you are not getting the same then what is happening to the $? If you are getting the same then the Search portion (with the -1) must surely be returning: {2;2;2;1;1;1;1;1;1;1} So it seems to me that you are saying that if you enter: $2 Win in cell A1 and then use the fomula: =LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1) <the equivalent of =LEFT(A1,2) > You are getting just 2 and I am getting $2 Is that right? 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 > When I examine the Left function results, I get 2 without the dollar sign. > I just tried several reformats and re-entries of the $2 Win text [quoted text clipped - 62 lines] >>>>>>> >>>>>>> Thanks I can swear something has changed, however.... > Very curious. When I examine just: A1:A10&"0 " I get: > {"$2 Win0 ";"$2 Win0 ";"$2 Win0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 "} > > If you are not getting the same then what is happening to the $? Yes, I am now getting the dollar signs. > If you are getting the same then the Search portion (with the -1) must > surely be returning: > {2;2;2;1;1;1;1;1;1;1} Yes, that is what I get also. > So it seems to me that you are saying that if you enter: $2 Win > in cell A1 and then use the fomula: [quoted text clipped - 5 lines] > > Is that right? Nope, I get the $2 for that formula also. However, if I put the double unary in front of it... =--LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1) it returns a 2 (which is why the SUMPRODUCT is producing the correct summation using Ron's original formula modified to include the correction I posted). If you prefix the above LEFT function with the double unary, are you saying you are not getting a 2 for a result? Rick Hi Rick, > Nope, I get the $2 for that formula also. However, if I put the double > unary in front of it... > > =--LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1) > > it returns a 2 That's the difference! I get #VALUE! I am using XL97 SR-1. I have never encountered any calculation difficulties until no but I have heard that there were several. I will see if SR-2 is still available on the MS site and see if that cures it. Thank you. SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I can swear something has changed, however.... > [quoted text clipped - 34 lines] > > Rick Hi Again Rick, Well I am all updated now, (if not up to date <g> ), but unfortunately it is still the same so I asume that it is a version thing. Oh well, I will just have to live with being the poor cousin. 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 > Hi Rick, > [quoted text clipped - 51 lines] >> >> Rick Yes, it probably is a version thing... I am using XL2003. Rick > Hi Again Rick, > [quoted text clipped - 57 lines] >>> >>> Rick <<<"Yes, it probably is a version thing... ">>> I really don't think so. This morning I was in town and was following this thread on an XP - XL02 machine, and the returns were - accurate totals, without dollar signs. This evening, I'm in the boonies, on a Win98 - XL97 SR-2 machine. I get *exactly* the same returns that I got in town on the XL02 machine. SO ... it has to be something else! SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Yes, it probably is a version thing... I am using XL2003. > [quoted text clipped - 61 lines] > >>> > >>> Rick Well, that it is interesting, to say the least. I looked and didn't see any (obvious) Option settings that would seem to control this, so I wonder what the problem with Sandy's Excel could be then? Rick > <<<"Yes, it probably is a version thing... ">>> > [quoted text clipped - 81 lines] >> >>> >> >>> Rick Don't you see Debra's post? Refers to Sandy's currency setting. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Well, that it is interesting, to say the least. I looked and didn't see any > (obvious) Option settings that would seem to control this, so I wonder what [quoted text clipped - 92 lines] > >> >>> > >> >>> Rick Yes, I saw it... but misunderstood her comment. I skimmed it and thought she was making a joke in response to his tag line. So, his (and many others) currency is not the dollar sign... this raises the "international audience" question then... theoretically, Sandy's inclusion of the SUBSTITUTE function would make the formula universally usable. Of course, the odds are that someone typing in "$2 Win" in his/her own spreadsheet would not be too concerned with Regional Setting's issues though.<g> Rick > Don't you see Debra's post? > [quoted text clipped - 107 lines] >> >> >>> >> >> >>> Rick With 97 SR-1, Each of [General format] A1:A3 contain the 6 bytes $2 Win =SUMPRODUCT(--(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1))) produces 6. So, all fine, Rick-nee-Ron. RR wrote: > When I examine the Left function results, I get 2 without the dollar sign. > I just tried several reformats and re-entries of the $2 Win text (including > with a leading apostrophe) and the dollar sign always gets removed and the > SUMPRODUCT always produces the correct summation. B1=LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1) gives two bytes here $2 I assume that's what you meant by "examine the Left" but do correct me if I misunderstood. Note that the LEFT result as I showed above (in isolation from sumproduct) gives a relative result (relative to current row), in case that tripped anyone up. Thus the same formula in B4 gives 0. So Rick, there's a $ on this version. Sandy, I don't duplicate, but perhaps I have different assumptions above. Or... Anybody, feel free to note new thread "Using multicell range when single cell prescribed" (featuring "VALUE" perhaps significant to earlier posts in this thread.) On Sat, 8 Sep 2007 17:48:46 -0400, "Rick Rothstein \(MVP - VB\)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote: >Yes, it probably is a version thing... I am using XL2003. > [quoted text clipped - 67 lines] >>>> >>>> Rick Hi Wild Bill, > B1=LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1) > gives two bytes here > $2 Yes it does, but what Rick was saying was that for him: =--LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1) returned 2 and for me it returned #VALUE! Debra nailed it when she said that it was a currency thing and when I change the $ to a £ it all works for me the same as the rest of you. > I assume that's what you meant by "examine the Left" but do correct me > if I misunderstood. I can't speak for Rick but what I was talking about was highlighting just: LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1) in the formula bar and pressing the function key F9 to calculate just that part of the formula. (If you try it, then to return to the formula either click on the Cancel "X" in the foruma bar or press the Esc or you will hard code the result of the part calculation into the formula. Please forgive me if you already knew this) 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 > With 97 SR-1, > Each of [General format] A1:A3 contain the 6 bytes $2 Win [quoted text clipped - 104 lines] >>>>> >>>>> Rick > In Perth, the ancient capital of Scotland > and the crowning place of kings And the land of the pound as default currency symbol? > Hi Again Rick, > > Well I am all updated now, (if not up to date <g> ), but unfortunately it is > still the same so I asume that it is a version thing. Oh well, I will just > have to live with being the poor cousin. SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html Thank you very much Debra! £2 Win works as advertised for me and I no longer feel hard done to! <g> 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 > > In Perth, the ancient capital of Scotland > > and the crowning place of kings [quoted text clipped - 6 lines] >> is still the same so I asume that it is a version thing. Oh well, I >> will just have to live with being the poor cousin. You're welcome! And a £2 Win is better anyway. <g> > Thank you very much Debra! > £2 Win works as advertised for me and I no longer feel hard done to! <g> SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html Hi, Rick 1) If the range may contain blank cells, yes....you need the zero you inserted to accommodate them. Of course, everything still falls apart if any cell only contains text or there's no space between the number and the text. There's usually a trade-off between over-engineering a formula and making it too limited to be used more broadly. 2) I purposely opted for the SEARCH function because it is not case-sensitive. It doesn't matter when searching for a space, but the formula is flexible enough to pull numbers prior to the first instance of any character. (see comment 1) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I think you left out a "0" in the first concatenation.... > [quoted text clipped - 28 lines] >>> >>> Thanks For point 1) I assumed you added the "0 " in the SEARCH function in order to handle blank cells... if that were the case, then the missing zero I pointed out for the other concatenation would be required.... otherwise, if there were no blank cells, you wouldn't need to concatenate anything within the formula at all... the following would work fine, assuming no blank cells within the range... =SUMPRODUCT(--(LEFT(A1:A10,SEARCH(" ",A1:A10)-1))) Rick > Hi, Rick > [quoted text clipped - 50 lines] >>>> >>>> Thanks |
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.