Prev: visible cell only
Next: Can I color a cell with "If" formula
From: tompl on 12 Apr 2010 18:01
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.
>
From: Joe User on 12 Apr 2010 18:23
"tompl" <tompl(a)discussions.microsoft.com> 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" <tompl(a)discussions.microsoft.com> wrote in message
news:E42A1634-3334-4B74-B27C-CA8DFCF01D8C(a)microsoft.com...
>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.
>>

From: tompl on 12 Apr 2010 19:08
I have version 2002 on this machine.

Loved you solution to YTM.

Tom
From: T. Valko on 13 Apr 2010 00:24
>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:e5hTH7o2KHA.5004(a)TK2MSFTNGP04.phx.gbl...
> "tompl" <tompl(a)discussions.microsoft.com> 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" <tompl(a)discussions.microsoft.com> wrote in message
> news:E42A1634-3334-4B74-B27C-CA8DFCF01D8C(a)microsoft.com...
>>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.
>>>
>


From: Chris on 13 Apr 2010 02:32
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" <biffinpitt(a)comcast.net> wrote in message
news:eKnypEs2KHA.4964(a)TK2MSFTNGP05.phx.gbl...
> >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:e5hTH7o2KHA.5004(a)TK2MSFTNGP04.phx.gbl...
>> "tompl" <tompl(a)discussions.microsoft.com> 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" <tompl(a)discussions.microsoft.com> wrote in message
>> news:E42A1634-3334-4B74-B27C-CA8DFCF01D8C(a)microsoft.com...
>>>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.
>>>>
>>
>
>

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: visible cell only
Next: Can I color a cell with "If" formula