Hi. Here is a conversation of Joe Smith with James Elliott: (Last Sat, 20 Sep 2008 00:11:42 -0400)
*********************** BEGIN **************************** James Elliott - WA Rural Computers wrote: > ... > So, in Calc (forget Excel) we can use either of these formats, can we?: > > =SUMPRODUCT(A1:A3000; D1:D3000<=TEST) > > or > > =SUMPRODUCT((A1:A3000)*(D1:D3000<=TEST)) > > Am I correct? Yes, I believe that is correct. Logical tests in Calc always produce numeric 0 or 1, so the simple arguments will work fine for Calc. That's not true for Excel, so if you need to move the same sheet between Calc and Excel, you must use the second form to force the logical test results to be converted to 0 or 1. It isn't /needed/ for Calc, but it works the same on both. <Joe *********************** END **************************** I hope it helps. 2008/9/22 Mark Knecht <[EMAIL PROTECTED]> > On Mon, Sep 22, 2008 at 11:02 AM, Joe Smith <[EMAIL PROTECTED]> wrote: > > Mark Knecht wrote: > >> > >> ... > >> I need to get the sum of column 1 * column 2 but only when the value > >> in column 1 is either negative or positive. > > > > Hmm, "either negative or positive"? SUMPRODUCT(A1:A5; B1:B5) will do > that, > > since your condition is true for every number I can think of. > > > > If you mean "only when the value in column 1 is not zero", you can use > > something like > > > > =SUMPRODUCT(A1:A5<>0; A1:A5; B1:B5) > > > > or "only when the value in column 1 is positive": > > > > =SUMPRODUCT(A1:A5>0; A1:A5; B1:B5) > > > > <Joe > > Joe, > The latter is what I was looking for: > > =SUMPRODUCT(A1:A5>0; A1:A5; B1:B5) > > Now, interestingly, while this does seem to work in Calc it doesn't > work in Excel. I've personally never seen the use of semi-colons in > Excel. Is there a form of this equation that you know of that would be > compatible with both Calc and Excel? > > Thanks, > Mark > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
