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.