SUMPRODUCT question

[Guest]

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)

 

[Guest]

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.

 

[Guest]

Thanks.....that makes sense now!

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.

 

[Guest]

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

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

--

HTH

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