Sumproduct Function
 

I use the Sumproduct() function extensively;
Obviously, there are also in use, significant Control-Array
Formulas (which I have used, but less often). Can
Someone tell me the what differentiates the two?
At some point (it seems) the Sumproduct lacks what
The CSE formulas can do, but I cannot express or explain it.
Any clarification would be appreciate.

Sumproduct Function
 

One difference is that you cannot use a straight forward IF in sumproduct
assume

you have this formula

=SUMPRODUCT(--(A1:A660),--(B1:B6="a"),C1:C6)

which will total c1:c6 if certain conditions in A and B are fulfilled
However if there would be an error like #N/A in C it will return that error
This array formula would fix that

=SUM(IF(A1:A660,IF(B1:B6="a",IF(ISNUMBER(C1:C6),C 1:C6))))


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"JimMay" wrote in message
news:8PXkg.41424$fG3.28516@dukeread09...
I use the Sumproduct() function extensively;
Obviously, there are also in use, significant Control-Array
Formulas (which I have used, but less often). Can
Someone tell me the what differentiates the two?
At some point (it seems) the Sumproduct lacks what
The CSE formulas can do, but I cannot express or explain it.
Any clarification would be appreciate.

Sumproduct Function
 

Thanks Peo,

I heard on Public Radio yesterday that I guy in California,
Who had just bought himself a new "Gold-Dector" put it to use
In his front yard "AND IT BEGAN RAPIDLY TICKING OFF THE SCALE"
He dug a hole 60 feet deep before the City insisted he stop
Digging and begin filling the Hole back up... LOL LOL

Jim May


"Peo Sjoblom" wrote in message
:

One difference is that you cannot use a straight forward IF in sumproduct
assume

you have this formula

=SUMPRODUCT(--(A1:A660),--(B1:B6="a"),C1:C6)

which will total c1:c6 if certain conditions in A and B are fulfilled
However if there would be an error like #N/A in C it will return that error
This array formula would fix that

=SUM(IF(A1:A660,IF(B1:B6="a",IF(ISNUMBER(C1:C6),C 1:C6))))


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"JimMay" wrote in message
news:8PXkg.41424$fG3.28516@dukeread09...
I use the Sumproduct() function extensively;
Obviously, there are also in use, significant Control-Array
Formulas (which I have used, but less often). Can
Someone tell me the what differentiates the two?
At some point (it seems) the Sumproduct lacks what
The CSE formulas can do, but I cannot express or explain it.
Any clarification would be appreciate.

Sumproduct Function
 

Yep, heard it on NPR as well <bg

--

Peo



"JimMay" wrote in message
news:6JYkg.41427$fG3.8372@dukeread09...
Thanks Peo,

I heard on Public Radio yesterday that I guy in California,
Who had just bought himself a new "Gold-Dector" put it to use
In his front yard "AND IT BEGAN RAPIDLY TICKING OFF THE SCALE"
He dug a hole 60 feet deep before the City insisted he stop
Digging and begin filling the Hole back up... LOL LOL

Jim May


"Peo Sjoblom" wrote in message
:

One difference is that you cannot use a straight forward IF in sumproduct
assume

you have this formula

=SUMPRODUCT(--(A1:A660),--(B1:B6="a"),C1:C6)

which will total c1:c6 if certain conditions in A and B are fulfilled
However if there would be an error like #N/A in C it will return that
error
This array formula would fix that

=SUM(IF(A1:A660,IF(B1:B6="a",IF(ISNUMBER(C1:C6),C 1:C6))))


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"JimMay" wrote in message
news:8PXkg.41424$fG3.28516@dukeread09...
I use the Sumproduct() function extensively;
Obviously, there are also in use, significant Control-Array
Formulas (which I have used, but less often). Can
Someone tell me the what differentiates the two?
At some point (it seems) the Sumproduct lacks what
The CSE formulas can do, but I cannot express or explain it.
Any clarification would be appreciate.