Re: [libreoffice-users] function sumif question

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