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.