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

At 16:19 08/09/2013 -0400, Fred James wrote:
> Brian Barker wrote:
> > 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!
>
> Sorry ... yes: x=y is the same as y=x, but when testing the original 
> function the results were incorrect.

We cannot know how, of course - but you must have "tested" it 
incorrectly!  (I tested it, too.)

> Switching the b's and c's made the function return the correct results.

But you haven't just done that: the comparison is still between the 
Bs and the E (though reversed) and the product is still between the 
B/E switch and the Cs (though also reversed).

> 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

Yes: this also works - but there is no need to modify the original 
formula.  Your chosen order of parts is not necessary for the formula 
to work.  I'm not finding fault with your formula, but you are - 
incorrectly - with mine!

> 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?

No!  In my original formula, the E value was still compared with the 
Bs and the product made between the resulting switch and the Cs.

> Or did I miss something?

Obviously yes: please go back and read my version again!

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

Good-oh!  I have to say that SUMPRODUCT() had not been the first 
thing I'd think of in situations such as this - but I picked up the 
powerful technique from experts on this and similar lists.

Brian Barker


--
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