SUMPRODUCT question
 

Hello all,

Every response that I've seen that includes SUMPRODUCT, I see all of the
experts using the "Binary Negetion Operator" (<---please correct my
terminology if incorrect). For example: =SUMPRODUCT(--([Some Range
=,<,,<=,= Some value or some cell ref]),--([Some Range]))

What is the purpose of the "Binary Negetion Operator"?

I've been the SUMPRODUCT function for a year or so now and have never used
the the double negative. Although, I do use the function in the other
manner: =SUMPRODUCT(([Some Range =,<,,<=,= Some Value or Some Cell
Ref])*([Some Range]))

Thanks for any help anyone can provide,

Conan Kelly

SUMPRODUCT question
 

The double negation simply converts a TRUE/FALSE value to 1/0 so SUMPRODUCT
can actually sum (One negative to convert TRUE/FALSE to -1/0, and the second
negative to convert back to positive... 1/0). It is the same as doing any
numerical calculation, so you could use
=SUMPRODUCT(1*([range1]),1*([range2])) as well. You don't know it but you're
doing the same thing in your example, by using ([Range1])*([Range2]) Excel is
performing a vector dot product, which is the numerical computation you need.

Make sense?
--
Regards,
Dave


"Conan Kelly" wrote:

Hello all,

Every response that I've seen that includes SUMPRODUCT, I see all of the
experts using the "Binary Negetion Operator" (<---please correct my
terminology if incorrect). For example: =SUMPRODUCT(--([Some Range
=,<,,<=,= Some value or some cell ref]),--([Some Range]))

What is the purpose of the "Binary Negetion Operator"?

I've been the SUMPRODUCT function for a year or so now and have never used
the the double negative. Although, I do use the function in the other
manner: =SUMPRODUCT(([Some Range =,<,,<=,= Some Value or Some Cell
Ref])*([Some Range]))

Thanks for any help anyone can provide,

Conan Kelly

SUMPRODUCT question
 

Have a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

this gives a very detailed explanation on all aspects of the sumproduct
formula.

Greetings from New Zealand





"Conan Kelly" wrote in message
...
Hello all,

Every response that I've seen that includes SUMPRODUCT, I see all of the
experts using the "Binary Negetion Operator" (<---please correct my
terminology if incorrect). For example: =SUMPRODUCT(--([Some Range
=,<,,<=,= Some value or some cell ref]),--([Some Range]))

What is the purpose of the "Binary Negetion Operator"?

I've been the SUMPRODUCT function for a year or so now and have never used
the the double negative. Although, I do use the function in the other
manner: =SUMPRODUCT(([Some Range =,<,,<=,= Some Value or Some Cell
Ref])*([Some Range]))

Thanks for any help anyone can provide,

Conan Kelly