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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

"Chris" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

It's a paren thing, try this:

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

Tom

"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.

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.
>

"tompl" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:E42A1634-3334-4B74-B27C-(E-Mail Removed)...
>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.
>>

I have version 2002 on this machine.

Loved you solution to YTM.

Tom

"tompl" wrote:
> I have version 2002 on this machine.

Good to know. For future reference, I guess we should enclose ranges in
parentheses when they are used in arithmetic expressions, unless we know that
the Excel version is later than 2002.

But since Chris is using Excel 2007, and I have no problem without
parentheses in Excel 2003, I ass-u-me that Chris also has no problem without
parentheses.


Off-topic....

> Loved you solution to YTM.

Thanks. But it was poor of me to use pv as a variable name. It conflicts
with the VBA function name. And the pv For-loop can be replaced with the
statement:

pv = (1-(1+r)^-n) / r

Finally, a Newton-Raphson approximation is probably a better algorithm than
my binary search. (Is that what Dana provided?) But I don't have any
experience writing with N-R algorithms. I can write binary searches in my
sleep :-).

>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:(E-Mail Removed)...
> "tompl" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:E42A1634-3334-4B74-B27C-(E-Mail Removed)...
>>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.
>>>
>