2013 Archives by date, by thread · List index

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

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