Can I use Sumproduct with the LEFT Function?

[Chris]

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]

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?

 

[Joe User]

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.

 

[tompl]

It's a paren thing, try this:

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

Tom

 

[Joe User]

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]

I did try it. It did not work without the parens and it did work with the
parens on my machine.

Tom

 

[Joe User]

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]

I have version 2002 on this machine.

Loved you solution to YTM.

Tom

 

[Joe User]

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 .

 

[T. Valko]

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)

 

[Chris]

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]

didn't mean to cause an argument though!

We're not arguing, we're just hashing things out!

Thanks for the feedback!