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.