From: Chris on
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
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
"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
It's a paren thing, try this:

=SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*($B$4:$B$1004))

Tom
From: Joe User on
"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.