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 !

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

--

HTH

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


"Eric @ CMN, Evansville" wrote
in message ...
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 !

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" wrote
in message ...
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 !

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 !

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 !