SUMPRODUCT question

[Eric @ CMN, Evansville]

I am not familiar with the SUMPRODUCT function. I asked someone to do some
work for me on a spreadsheet and he added the following expression to a cell:

=SUMPRODUCT(--('PO Log'!$AB$2:$AB$1203='Summary by Line Equivalent'!A2),('PO
Log'!$I$2:$I$1203),--('PO Log'!$AA$2:$AA$1203<>"CANCELLED"),--(PO
Log'!$X$2:$X$1203<>""))

I've been trying to understand exactly what this is doing, and I can't seem
to find anything that helps me understand what the "--" is for. Can someone
put this expression into English for me ?

Thanks !

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Eric @ CMN, Evansville" <[email protected]> wrote
in message news:[email protected]...
> I am not familiar with the SUMPRODUCT function. I asked someone to do some
> work for me on a spreadsheet and he added the following expression to a
cell:
>
> =SUMPRODUCT(--('PO Log'!$AB$2:$AB$1203='Summary by Line
Equivalent'!A2),('PO
> Log'!$I$2:$I$1203),--('PO Log'!$AA$2:$AA$1203<>"CANCELLED"),--(PO
> Log'!$X$2:$X$1203<>""))
>
> I've been trying to understand exactly what this is doing, and I can't
seem
> to find anything that helps me understand what the "--" is for. Can
someone
> put this expression into English for me ?
>
> Thanks !

[Sloth]

People use the SUMPRODUCT function to perform fancy COUNTIF or SUMIF
procedures. In your example you can break the FUNCTION in three parts, each
part results in an array.

(--('PO Log'!$AB$2:$AB$1203='Summary by Line Equivalent'!A2)
creates an array of 1's and 0's (the -- turns a logical value into a
numerica value True=1, False=0). Depending on if the cell in column AB of
one sheet equals A2 from the other sheet. It creates an array that will look
like this
{1,1,0,1,0,1...1,0,1}

the other two are also logical functions that result in an array of 1's an
0's.

Each cell in each resulting array is mulitplied to each other to create a
final array of 1's and 0' which is summed.

For example if the following arrays were to be mulitplied
{1,0,1,0}
{0,1,1,0}
{1,1,1,1}
the result would be
{0,0,1,0}
and the sum would be 1.

Hope I have been clear. Once you understand this manipulation of SUMPRODUCT
you can create extremely usefull functions.

"Eric @ CMN, Evansville" wrote:

> I am not familiar with the SUMPRODUCT function. I asked someone to do some
> work for me on a spreadsheet and he added the following expression to a cell:
>
> =SUMPRODUCT(--('PO Log'!$AB$2:$AB$1203='Summary by Line Equivalent'!A2),('PO
> Log'!$I$2:$I$1203),--('PO Log'!$AA$2:$AA$1203<>"CANCELLED"),--(PO
> Log'!$X$2:$X$1203<>""))
>
> I've been trying to understand exactly what this is doing, and I can't seem
> to find anything that helps me understand what the "--" is for. Can someone
> put this expression into English for me ?
>
> Thanks !

[Eric @ CMN, Evansville]

Thanks.....that makes sense now!

"Sloth" wrote:

> People use the SUMPRODUCT function to perform fancy COUNTIF or SUMIF
> procedures. In your example you can break the FUNCTION in three parts, each
> part results in an array.
>
> (--('PO Log'!$AB$2:$AB$1203='Summary by Line Equivalent'!A2)
> creates an array of 1's and 0's (the -- turns a logical value into a
> numerica value True=1, False=0). Depending on if the cell in column AB of
> one sheet equals A2 from the other sheet. It creates an array that will look
> like this
> {1,1,0,1,0,1...1,0,1}
>
> the other two are also logical functions that result in an array of 1's an
> 0's.
>
> Each cell in each resulting array is mulitplied to each other to create a
> final array of 1's and 0' which is summed.
>
> For example if the following arrays were to be mulitplied
> {1,0,1,0}
> {0,1,1,0}
> {1,1,1,1}
> the result would be
> {0,0,1,0}
> and the sum would be 1.
>
> Hope I have been clear. Once you understand this manipulation of SUMPRODUCT
> you can create extremely usefull functions.
>
> "Eric @ CMN, Evansville" wrote:
>
> > I am not familiar with the SUMPRODUCT function. I asked someone to do some
> > work for me on a spreadsheet and he added the following expression to a cell:
> >
> > =SUMPRODUCT(--('PO Log'!$AB$2:$AB$1203='Summary by Line Equivalent'!A2),('PO
> > Log'!$I$2:$I$1203),--('PO Log'!$AA$2:$AA$1203<>"CANCELLED"),--(PO
> > Log'!$X$2:$X$1203<>""))
> >
> > I've been trying to understand exactly what this is doing, and I can't seem
> > to find anything that helps me understand what the "--" is for. Can someone
> > put this expression into English for me ?
> >
> > Thanks !

[Eric @ CMN, Evansville]

Thanks...I'll review the link you posted.

"Bob Phillips" wrote:

> See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Eric @ CMN, Evansville" <[email protected]> wrote
> in message news:[email protected]...
> > I am not familiar with the SUMPRODUCT function. I asked someone to do some
> > work for me on a spreadsheet and he added the following expression to a
> cell:
> >
> > =SUMPRODUCT(--('PO Log'!$AB$2:$AB$1203='Summary by Line
> Equivalent'!A2),('PO
> > Log'!$I$2:$I$1203),--('PO Log'!$AA$2:$AA$1203<>"CANCELLED"),--(PO
> > Log'!$X$2:$X$1203<>""))
> >
> > I've been trying to understand exactly what this is doing, and I can't
> seem
> > to find anything that helps me understand what the "--" is for. Can
> someone
> > put this expression into English for me ?
> >
> > Thanks !
>
>
>