software

I needed a formula (that I was able to find on this group, btw). The formula
from the gropu was:

=sumproduct(--(B2:B10=1),--(F2:F10=quot;Completedquot;))

My question is, what does the -- do, and can I find more information about
this in the help files? If it is in help, what is it called, as I haven't
been able to find anything about it - even when looking at the sumproduct
help information.

Thanks,


-- is known as double unary minus, its function in a formula like

=sumproduct(--(B2:B10=1),--(F2:F10=quot;Completedquot;))

is to convert an array of TRUE/FALSE values to 1/0, e.g.

(B2:B10=1) returns an array like

{TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE}

-- coerces this to

{1,0,1,1,0,0,1,1,1}

the same happens with

--(F2:F10=quot;Completedquot;)

SUMPRODUCT then multiplies the two arrays (giving 1 only when both
arrays are 1) and then sums the resultant single array thereby
effectively counting the rows where both conditions are true. The
arrays can be co-erced in other ways, e.g. by adding zero or
multiplying by 1, so you could as easily use

=sumproduct((B2:B10=1) 0,(F2:F10=quot;Completedquot;)*1)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=533005See www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. It isn't in help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Poodyquot; gt; wrote in message
...
gt; I needed a formula (that I was able to find on this group, btw). The
formula
gt; from the gropu was:
gt;
gt; =sumproduct(--(B2:B10=1),--(F2:F10=quot;Completedquot;))
gt;
gt; My question is, what does the -- do, and can I find more information about
gt; this in the help files? If it is in help, what is it called, as I haven't
gt; been able to find anything about it - even when looking at the sumproduct
gt; help information.
gt;
gt; Thanks,
Thank you both very much - that is great information.

Rich

quot;Bob Phillipsquot; wrote:

gt; See www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
gt; explanation. It isn't in help.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Poodyquot; gt; wrote in message
gt; ...
gt; gt; I needed a formula (that I was able to find on this group, btw). The
gt; formula
gt; gt; from the gropu was:
gt; gt;
gt; gt; =sumproduct(--(B2:B10=1),--(F2:F10=quot;Completedquot;))
gt; gt;
gt; gt; My question is, what does the -- do, and can I find more information about
gt; gt; this in the help files? If it is in help, what is it called, as I haven't
gt; gt; been able to find anything about it - even when looking at the sumproduct
gt; gt; help information.
gt; gt;
gt; gt; Thanks,
gt;
gt;
gt;