Re: [libreoffice-users] spreadsheet - sum one column based on the value of other

Brian Barker wrote:
> At 13:42 08/09/2013 -0400, Fred James wrote:
> > Brian Barker wrote:
> > > =SUMPRODUCT(B$2:B$5=E1;C$2:C$5)
> > > so you can fill this down into F2.
> >
> > Typo found :  switch the B's and C's so that the function reads:
> >     =SUMPRODUCT(C$2:C$7,E1=B$2:B$7)
> > and it works.
>
> Er, there is no "typo", just some confusion on your part.  The 
> original formula works.  You have changed the test from X = Y to Y = X 
> and the product X x Y into Y x X.  As any elementary mathematician 
> knows, equality and multiplication are commutative, so these are the 
> same thing: x = y and y = x are the same test; and 2 x 3 and 3 x 2 are 
> the same value!
>
> Brian Barker
Sorry ... yes: x=y is the same as y=x, but when testing the original 
function the results were incorrect.  Switching the b's and c's made the 
function return the correct results.  Assumption (on my part):
    the first part (C$2:C$5) is the column of values to be summed
    the second part (E1=) is the value we want to match in the third part
    the third part (B$2:B$5) is the column/range of values to be 
matched by the second part
¿Sí?
So if we used column B for the first part we would be trying to sum 
labels/text, and if we used C in the third part, we would be trying to 
match labels/text to numbers?

Or did I miss something?

In any case, I must thank you for pointing to this solution ... it is 
great ... I really enjoyed this.
Regards
Fred James



--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted