SUMPRODUCT question
 

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 !

SUMPRODUCT question
 

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 !

SUMPRODUCT question
 

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 !

SUMPRODUCT question
 

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 !

SUMPRODUCT question
 

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 !