sumproduct question - kind of...
 

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="Completed"))

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,

sumproduct question - kind of...
 

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

=sumproduct(--(B2:B10=1),--(F2:F10="Completed"))

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="Completed")

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="Completed")*1)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=533005

sumproduct question - kind of...
 

See http://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)

"Poody" wrote in message
...
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="Completed"))

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,

sumproduct question - kind of...
 

Thank you both very much - that is great information.

Rich

"Bob Phillips" wrote:

See http://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)

"Poody" wrote in message
...
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="Completed"))

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,