Can I use Sumproduct with the LEFT Function?
in [Functions]
|
From: Chris on 12 Apr 2010 13:27 Hi, I'm using xl 2007. I need to sum figures in column B if the relevant cell in column F has an entry that starts SM. I was trying to use SUMPRODUCT but I can't seem to get the right way of using the LEFT function to stipulate when to include the figure in col B. I was hoping not to use an array formula just because I'm the only person here who understands them. My question is; as SUMPRODUCT is numerical and LEFT is text based, is that a bad mix? if so, how should I go about it please? I know I could hive off the first 2 characters and mark a cell 1 or 0 according to if they match SM but if I did that I would want to hide those columns and would prefer a one formula solution if possible. Thanks Chris
From: Chris on 12 Apr 2010 13:36 Ok, I think I have answered my own question with this =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*$B$4:$B$1004) can anyone see anything wrong with that please? "Chris" <chris.smith(a)zeronet.co.uk> wrote in message news:O7rxFWm2KHA.3844(a)TK2MSFTNGP05.phx.gbl... > Hi, I'm using xl 2007. I need to sum figures in column B if the relevant > cell in column F has an entry that starts SM. I was trying to use > SUMPRODUCT but I can't seem to get the right way of using the LEFT > function to stipulate when to include the figure in col B. I was hoping > not to use an array formula just because I'm the only person here who > understands them. My question is; as SUMPRODUCT is numerical and LEFT is > text based, is that a bad mix? if so, how should I go about it please? I > know I could hive off the first 2 characters and mark a cell 1 or 0 > according to if they match SM but if I did that I would want to hide those > columns and would prefer a one formula solution if possible. > Thanks > Chris
From: Joe User on 12 Apr 2010 14:01 "Chris" <chris.smith(a)zeronet.co.uk> wrote in message news:e85b6am2KHA.4912(a)TK2MSFTNGP06.phx.gbl... > Ok, I think I have answered my own question with this > =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*$B$4:$B$1004) > can anyone see anything wrong with that please? Nothing wrong per se. But I would be inclined to write: =SUMPRODUCT(--(LEFT($F$4:$F$1004,2)="SM"),$B$4:$B$1004) Functionally equivalent. But the latter form works even B4:B1004 contains text, notably null strings (""), which is not uncommon.
From: tompl on 12 Apr 2010 14:22 It's a paren thing, try this: =SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*($B$4:$B$1004)) Tom
From: Joe User on 12 Apr 2010 17:33
"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. |