"starguy" wrote:
Max would you please explain the working of -- in SUMPRODUCT formula. I
have read the article at xldynamic but could not understand the --
functionality.

The "--" is what they call a double unary which coerces the TRUE / FALSE
returns in the comparisons evaluated within the parens to numeric 1's / 0's

Example:
=SUMPRODUCT(--(A1:C3<""))

Supposing we have inputs made within A1:C3 , say specifically in A2, B3 and
C1 (with all other cells within A1:C3 either "blank"* or empty)
*eg: there may be formulas within A1:C3 returning zero length null strings: ""

Then in A4: =SUMPRODUCT(--(A1:C3<""))

would resolve initially to:
=SUMPRODUCT(--({FALSE,FALSE,TRUE;TRUE,FALSE,FALSE;FALSE,TRUE,FAL SE}))

We can see the above happen by carefully selecting the innermost part of the
formula, viz. just the part: A1:C3<""
in the formula bar, then press F9 key

and then resolve to:
=SUMPRODUCT({0,0,1;1,0,0;0,1,0})
[ To see the above, just select the 2nd layer part viz.: --(A1:C3<"")
then press F9]

The "--" will coerce FALSEs to 0's, TRUEs to 1's
The 0's and 1's finally gets evaluated by the SUMPRODUCT
and returns in A4: 3

Try also JE McGimpsey's page for a good explanation at:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
Why use -- in SUMPRODUCT formulae

And .. Jason Morin explains it in depth in this past post
(double unary technique popularized by Harlan Grove):
http://tinyurl.com/fnt7v

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---