Can I use Sumproduct with the LEFT Function?
in [Functions]
|
From: T. Valko on 13 Apr 2010 14:31 >didn't mean to cause an argument though! We're not arguing, we're just hashing things out! Thanks for the feedback! -- Biff Microsoft Excel MVP "Chris" <chris.smith(a)zeronet.co.uk> wrote in message news:euH2tMt2KHA.3568(a)TK2MSFTNGP04.phx.gbl... > Thanks chaps - didn't mean to cause an argument though! > > My version is working so far without a problem and I always beleive in > having as few parenthese as possible to avoid confusion like Biff said. I > was surprised also to see Biff's SUMIF formula and it reminded me that > sometimes we are too careful to be exact these days. I'm sure when I was > (much) younger I would have used "SM*" without thinking twice. > > regards > > Chris > > "T. Valko" <biffinpitt(a)comcast.net> wrote in message > news:eKnypEs2KHA.4964(a)TK2MSFTNGP05.phx.gbl... >> >Can anyone else confirm Tom's observation? >> >> Both versions work just fine on my copy of Excel 2002 (no TEXT in column >> B, just numbers). >> >> =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*$B$4:$B$1004) >> =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*($B$4:$B$1004)) >> >> I think the use of superfluous parentheses just makes the formula harder >> to read. >> >> You could also use: >> >> =SUMIF($F$4:$F$1004,"SM*",$B$4:$B$1004) >> >> Which will ignore any text entries in column B as will: >> >> =SUMPRODUCT(--(LEFT($F$4:$F$1004,2)="SM"),$B$4:$B$1004) >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Joe User" <joeu2004> wrote in message >> news:e5hTH7o2KHA.5004(a)TK2MSFTNGP04.phx.gbl... >>> "tompl" <tompl(a)discussions.microsoft.com> wrote: >>>> I did try it. It did not work without the parens and it did work >>>> with the parens on my machine. >>> >>> I am using Excel 2003; no problem, as I said. I copy-and-pasted Chris's >>> formula verbatim. >>> >>> What Excel version are you using? >>> >>> Perhaps B4:B1004 requires parentheses in an earlier Excel version. I >>> would be surprised (well, only a little :-<) if it no longer works in >>> later Excel versions. Can anyone else confirm Tom's observation? >>> >>> Anyway, it is unclear whether Chris encountered an error when he/she >>> tried to enter the formula, or if Chris was merely asking if anyone >>> could foresee a functional problem with a formula that seems to work >>> when he/she tried it. The latter is my interpretation of Chris's >>> question. >>> >>> >>> ----- original message ----- >>> >>> "tompl" <tompl(a)discussions.microsoft.com> wrote in message >>> news:E42A1634-3334-4B74-B27C-CA8DFCF01D8C(a)microsoft.com... >>>>I did try it. It did not work without the parens and it did work with >>>>the >>>> parens on my machine. >>>> >>>> Tom >>>> >>>> "Joe User" wrote: >>>> >>>>> "tompl" wrote: >>>>> > It's a paren thing, try this: >>>>> > =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM") >>>>> > *($B$4:$B$1004)) >>>>> >>>>> Parentheses are not required around the range B4:B1004. I saw no >>>>> syntax >>>>> error in Chris's original posting, to wit: >>>>> >>>>> =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*$B$4:$B$1004) >>>>> >>>>> You should try it before commenting. >>>>> >>> >> >> > |