Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.