First off, I wasn't sure where to post this because I didn't know if the
-- is classified as a worksheet function or not, but what does it do?
I've seen them in a bunch of posts. I am just curious what they do.

Example:

=SUMPRODUCT(--($D$1:$D$100="100"),$F$1:$F$100)

versus

=SUMPRODUCT(($D$1:$D$100="100"),$F$1:$F$100)

Why does the second one not work?

Thanks,
Nick


--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=490898

-- can be used to convert non-numerics to numbers.

If A1 contained the text '123, then
=--A1 will contian the number 123.

It also can be used to convert True/False to +1/0. (-true = -1, --true = +1)

=sumproduct() likes to work with numbers so the first formula gets those
trues/falses converted to 1/0s.

But you can also write the formula this way:
=SUMPRODUCT(($D$1:$D$100="100")*($F$1:$F$100))

Just the act of multiplying is enough to coerce the true/false to 1/0.

=0+A1
=1*a1
will do the same kind of thing in that first example.

thekovinc wrote:

First off, I wasn't sure where to post this because I didn't know if the
-- is classified as a worksheet function or not, but what does it do?
I've seen them in a bunch of posts. I am just curious what they do.

Example:

=SUMPRODUCT(--($D$1:$D$100="100"),$F$1:$F$100)

versus

=SUMPRODUCT(($D$1:$D$100="100"),$F$1:$F$100)

Why does the second one not work?

Thanks,
Nick

--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=490898

--

Dave Peterson

Thank you very much!


--
thekovinc
------------------------------------------------------------------------
thekovinc's Profile: http://www.excelforum.com/member.php...o&userid=29378
View this thread: http://www.excelforum.com/showthread...hreadid=490898

Hi Nick

The double unary minus "--" coerces the TRUE or FALSE result of the
comparison inside the first set of brackets into 1's or 0's. This could also
be achieved by adding +0 or multiplying with a *1 addition to the formula
but doing a minus, minus (which comes back to the original value) is a
little faster in execution.

The resulting 1's and 0's then multiply the array from the second range and
Sumproduct adds the results to give the final answer.

So if the value in cell D1 were 100, that would be TRUE, and coerced to 1
would be multiplied by the value in F1 would result in the value in F1.
If D2 were not 100, then it would be FALSE hence 0 which multiplying by the
value in F2 would result in 0.


Regards

Roger Govier


thekovinc wrote:
First off, I wasn't sure where to post this because I didn't know if the
-- is classified as a worksheet function or not, but what does it do?
I've seen them in a bunch of posts. I am just curious what they do.

Example:

=SUMPRODUCT(--($D$1:$D$100="100"),$F$1:$F$100)

versus

=SUMPRODUCT(($D$1:$D$100="100"),$F$1:$F$100)

Why does the second one not work?

Thanks,
Nick