Sumproduct Function

[JimMay]

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.

 

[Peo Sjoblom]

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

you have this formula

=SUMPRODUCT(--(A1:A6>60),--(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:A6>60,IF(B1:B6="a",IF(ISNUMBER(C1:C6),C1: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]

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]

Yep, heard it on NPR as well <bg>

--

Peo

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