software

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$1203lt;gt;quot;CANCELLEDquot;),--(PO
Log'!$X$2:$X$1203lt;gt;quot;quot;))

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 quot;--quot; is for. Can someone
put this expression into English for me ?

Thanks !

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

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Eric @ CMN, Evansvillequot; gt; wrote
in message ...
gt; I am not familiar with the SUMPRODUCT function. I asked someone to do some
gt; work for me on a spreadsheet and he added the following expression to a
cell:
gt;
gt; =SUMPRODUCT(--('PO Log'!$AB$2:$AB$1203='Summary by Line
Equivalent'!A2),('PO
gt; Log'!$I$2:$I$1203),--('PO Log'!$AA$2:$AA$1203lt;gt;quot;CANCELLEDquot;),--(PO
gt; Log'!$X$2:$X$1203lt;gt;quot;quot;))
gt;
gt; I've been trying to understand exactly what this is doing, and I can't
seem
gt; to find anything that helps me understand what the quot;--quot; is for. Can
someone
gt; put this expression into English for me ?
gt;
gt; 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.

quot;Eric @ CMN, Evansvillequot; wrote:

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

Thanks.....that makes sense now!

quot;Slothquot; wrote:

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

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

quot;Bob Phillipsquot; wrote:

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