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


At 14:47 21/04/2016 -0700, John R. Sowden wrote:
I have an accounting ledger in calc, with two columns in question. col 1 is credit (amount charged to account) and col 2 is the account number (a 4 digit number currently formatted a number with no decimal, but not text). My objective is to sum the amounts in column 1 if col 2=1234. Not a problem. Done.

So you will have something like
=SUMIF(B1:B99;"1234";A1:A99)

Now I want to sum the amounts in column 1 if the number in col 2 is 1234 or 2345 or 6789. I tried just putting the numbers in the sumif field, I tried putting them in parentheses, and finally I tried the OR() function by making the criteria field in sumif OR(1234,2345,6789). No dice.

No, the OR() function won't help you here, as it expects boolean (logical) arguments, not numbers.

Two questions:
1) what is the solution to my problem?

You might be tempted by the SUMIFS() function introduced in version 4, which allows a combination of criteria. But the logic it uses is to match any entry that matches all criteria, not any of them; in other words, it combines the conditions using AND logic and not the OR logic you need here. So that's no help.

There are a number of ways to do this. Here are a few:

1. The sum you need is simply the sum of the sums for each account number. So you can add three expressions like your original:
=SUMIF(B1:B99;"1234";A1:A99)+SUMIF(B1:B99;"2345";A1:A99)+SUMIF(B1:B99;"6789";A1:A99)

2. (As has already been suggested ...) You can use the disjunction operator, |, like this:
=SUMIF(B1:B99;"=1234|2345|6789";A1:A99)
But notice that for this to work, you need to ensure that the relevant option is ticked at Tools | Options... | OpenOffice Calc | Calculate | Enable regular expressions in formulae.

2. An alternative way of selecting and summing vales is to use SUMPRODUCT(). Your original calculation could have been expressed as
=SUMPRODUCT(A1:A99;B1:B99=1234)
When interpreted as numbers, the boolean values of the second argument are seen as one for TRUE and zero for FALSE, so only the TRUE values are summed. Again, you could use this multiple times with something like
=SUMPRODUCT(A1:A99;B1:B99=1234)+SUMPRODUCT(A1:A99;B1:B99=2345)+SUMPRODUCT(A1:A99;B1:B99=6789)

3. Here is a more complex but perhaps neat idea. Try
{=SUM(SUMIF(B1:B99;{"=1234";"=2345";"=6789"};A1:A99))}
The internal set of braces creates an array of criteria for the SUMIF() function - as you require. The outer SUM() function then adds the values for the three criteria. Note carefully that this is an array formula and must be entered as such. You need to type the internal set of braces yourself, but you cannot do that with the outer set. Instead, you type the formula without those outer braces and then complete the entry by pressing Ctrl+Shift+Enter instead of simple Enter. The outer braces will appear automatically in the Input Line.

Incidentally, you may need to take care when using formulae such as these, as some will sum more than once any entry which satisfies more than one criterion. But that won't affect you here, as no account number can simultaneously have more the one of the values tested.

20 Where can I go for more reference material than the help screen to solve these little problems?

Er, have I missed eighteen questions?!

o The Calc Guide (available from the web site), specifically Chapters 7 and 13 and Appendix B.
o General web searches.
o This mailing list!

I trust this helps.

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

Context


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.