formula question

[Paula]

what these two lines do in formula, please

=SUMPRODUCT(--($K$17:$K$200=O17)*--($G$17:$G$200="s"))

after SUMPRODUCT(--

after =017)*--

I use them for differant formula an they work, but i don't know what
they mean

ty

 

[Jimmy Pena]

From http://www.contextures.on.ca/xlFunctions02.html:

The double unary (--) converts text to a number, and will work correctly
even if the lookup values are numbers.


HTH,
JP

 

[Dave Peterson]

If the formula actually multiplies the factors (using the *), then the -- aren't
required.

=SUMPRODUCT(($K$17:$K$200=O17)*($G$17:$G$200="s"))
would be sufficient.

But if you wrote the formula as:
=SUMPRODUCT(--($K$17:$K$200=O17),--($G$17:$G$200="s"))
Then the first minus convert true/falses to -1/0.
The second converts them to +1/0.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html