Sumproduct help

[Joneye]

What does the "--" relate to in the formula. I know I need to use it with words and then numbers. But a colleague has asked me what it relates to and how it works.

Is it a syntax?


=SUMPRODUCT(--($B$65:$B$75=$B80),--($D$65:$D$75))

 

[T. Valko]

What does the "--" relate to in the formula. I know I need to use it with words and then numbers. But a colleague has asked me what it relates to and how it works.

Is it a syntax?


=SUMPRODUCT(--($B$65:$B$75=$B80),--($D$65:$D$75))

See these:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[VoG]

-- coerces an array of False/True to an array of 0/1 that SUMPRODUCT can process.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mrexcel.com/forum/showpost.php?p=337954&postcount=8

 

[mancemonster]

I believe it changes the result of a true/false enqiery into 1 or 0.

In the example given...


=SUMPRODUCT(--($B$65:$B$75=$B80),--($D$65:$D$75))

The -- are net required in the formula.

The -- indicate which of B65-B75= B80 returning 1 if it does or 0 if not.

1*the corresponding item in D65-D75 returnes the reqiered value.
0*the corresponding item in D65-D75 returnes 0.

Add these up and Bobs your uncle!

As for wheter its a syntax... Not my experties I'm affraid!

Anyone else?

Regards


Roger

 

[Joneye]

I also found this a great help


http://en.wikipedia.org/wiki/Unary_operation

PS THANKS AGAIN GUYS