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$10 04)

can anyone see anything wrong with that please?


"Chris" wrote in message
...
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" wrote in message
...
Ok, I think I have answered my own question with this
=SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*$B$4:$B$10 04)
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$1 004))

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$10 04)

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$10 04)

You should try it before commenting.

"tompl" 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" wrote in message
...
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$10 04)

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$10 04)
=SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*($B$4:$B$1 004))

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
...
"tompl" 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" wrote in message
...
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$10 04)

You should try it before commenting.

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" wrote in message
...
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$10 04)
=SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*($B$4:$B$1 004))

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
...
"tompl" 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" wrote in message
...
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$10 04)

You should try it before commenting.

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" wrote in message
...
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" wrote in message
...
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$10 04)
=SUMPRODUCT((LEFT($F$4:$F$1004,2)="SM")*($B$4:$B$1 004))

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
...
"tompl" 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" wrote in message
...
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$10 04)

You should try it before commenting.