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.

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" <[email protected]> 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.
>
>

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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:[email protected]:

> 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" <[email protected]> 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.
> >
> >

Yep, heard it on NPR as well <bg>

--

Peo



"JimMay" <[email protected]> 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:[email protected]:
>
>> 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" <[email protected]> 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.
>> >
>> >
>