Sumproduct
 

I have a question about the use of Sumproduct to match multiple
conditions. While the calculations work as expected when I follow the
guide at this site:

http://www.contextures.on.ca/xlFunct...tml#SumProduct

What is the difference between SUMPRODUCT(--(A1:A10=0)) and
SUMPRODUCT((A1:A10=0))?

I am confused by the -- syntax. I have looked around and can't seem to
find any explanation for what it means. If I don't include them in my
Sumproduct calculations, the answers are incorrect. What gives?


Marc

Sumproduct
 

Give this link a try...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"meckhert" wrote:

I have a question about the use of Sumproduct to match multiple
conditions. While the calculations work as expected when I follow the
guide at this site:

http://www.contextures.on.ca/xlFunct...tml#SumProduct

What is the difference between SUMPRODUCT(--(A1:A10=0)) and
SUMPRODUCT((A1:A10=0))?

I am confused by the -- syntax. I have looked around and can't seem to
find any explanation for what it means. If I don't include them in my
Sumproduct calculations, the answers are incorrect. What gives?


Marc

Sumproduct
 

(A1:A10=0)

Returns {True,False,True,True,False,True,True,False,True,F alse}

But what you want is {1,0,1,1,0,1,1,0,1,0}

By using the first negative sign, you converse the Boolean values to numbers
{-1,0,-1,-1,0,-1,-1,0,-1,0}

Then negating this with the second negative sign, you get what you want
{1,0,1,1,0,1,1,0,1,0}

You could see this yourself by entering the formula. then highlight
(A1:A10=0) in the formula bar and hit F9.

Now hit escape to return it to being a formula. Now select
-(A1:A10=0) and hit F9, then escape when done looking.

Now select
--(A1:A10=0) and hit F9, then escape when done looking

Using F9 to do an immediate evaluation is often handy when working with
array formulas.
--
Regards,
Tom Ogilvy


"meckhert" wrote in message
oups.com...
I have a question about the use of Sumproduct to match multiple
conditions. While the calculations work as expected when I follow the
guide at this site:

http://www.contextures.on.ca/xlFunct...tml#SumProduct

What is the difference between SUMPRODUCT(--(A1:A10=0)) and
SUMPRODUCT((A1:A10=0))?

I am confused by the -- syntax. I have looked around and can't seem to
find any explanation for what it means. If I don't include them in my
Sumproduct calculations, the answers are incorrect. What gives?


Marc