sumproduct question - kind of...

[Poody]

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,

[daddylonglegs]

-- 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)

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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,

[Poody]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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,
>
>
>