On Conditional Sums
Posted by Cliff on September 05, 2001 3:41 PM
This may have already been covered in this group, but I don't recall seeing it and a re-emphasis won't hurt anyway. Most of what I have seen here uses the Boolean logic AND function, where both conditions must be TRUE to give a TRUE result. This appears in the form {=sum(array1="x")*(array2="y")}, where the "*" operator multiplies (or AND's) the two arrays. Think of TRUE as 1 and FALSE as 0, then 0x0=0, 1x0=0, 0x1=0 , and 1x1 =1
However, there is another Boolean function that can be useful; the OR function, which means that if either condition is TRUE then the ouput is TRUE. For example, 0+0=0, 0+1=1, 1+0=1, and 1+1=1. The following example combines the OR function with the AND function, by choosing two alternatives (OR) from column A that meet the criterion column B is an "h" (AND). In other words, in this example, I want the number of all "ana" that are "h" and all "bal" that are "h". The "+" operator is used here to achieve the OR function.
A B
---------
ana h
bal a
bos a
bos h
ana a
bos a
bos a
bal a
ana h
ana h
{=SUM(((A1:A10="ana")+(A1:A10="bal"))*(B1:B10="h"))} = 3. Use Shift+Control+Enter to make this an array formula.
This technique can be extended to many criteria and the combinations of AND's and OR's can be as complex as you want. Just be sure to keep the order of operations correct. For further information about using Boolean logic in this fashion, do a web-search for "truth table".
Cliff
Posted by IML on September 05, 2001 4:04 PM
Very interesting. I'll just throw this out as well,
=SUM((A1:A10={"BAL","ANA"})*(B1:B10="h"))
I believe will produce the same results. Always good to have another arrow in the quiver.
Posted by Mark W. on September 05, 2001 4:05 PM
Yep, this is 1+0 :) (NT)
Cliff
Posted by Aladin Akyurek on September 05, 2001 4:28 PM
Hi Cliff,
I thought we had a few examples around here, but not much.
Your example of (multi)conditional count is interesting, which can also be done with a "non-array" formula:
=SUMPRODUCT(((A1:A10="ana")+(A1:A10="bal"))*(B1:B10="h"))
If you once know how to do multiconditional count or sum by means of an array or a sumproduct formula, one that needs OR is easy to construct:
{=SUM((A1:A10="ana")*(B1:B10="h"))} in one cell and
{=SUM((A1:A10="bal")*(B1:B10="h")) in another, then
=SUM(cell1,cell2) comes easily.
Or this way:
{=SUM(((A1:A10="ana")*(B1:B10="h")+SUM((A1:A10="bal"))*(B1:B10="h")))}
Remembering one's school algebra, XY+XZ = X (Y+Z), you'd hope to get the formula that you posted. But, that's the hard part.
Aladin
========= This may have already been covered in this group, but I don't recall seeing it and a re-emphasis won't hurt anyway. Most of what I have seen here uses the Boolean logic AND function, where both conditions must be TRUE to give a TRUE result. This appears in the form {=sum(array1="x")*(array2="y")}, where the "*" operator multiplies (or AND's) the two arrays. Think of TRUE as 1 and FALSE as 0, then 0x0=0, 1x0=0, 0x1=0 , and 1x1 =1 A B
Posted by Aladin A on September 05, 2001 4:38 PM
Posted by Aladin Akyurek on September 05, 2001 4:42 PM
I wasn't...
planning to post...the web script around here accepts any thing. : Very interesting. I'll just throw this out as well, : =SUM((A1:A10={"BAL","ANA"})*(B1:B10="h")) : I believe will produce the same results. Always good to have another arrow in the quiver.
Posted by Cliff on September 05, 2001 8:04 PM
I tried this first, but I didn't realize that I needed to have {"BAL","ANA"} in braces.
Thanks for pointing that out.
Cliff
