In array formulas, you generally use multiplication to get an AND
operator and addition to get an OR operator. Multiplication works as
an AND operator because it returns TRUE (<0) only if both operands
are not equal to zero, just as an AND truth table would show.
Similarly, addition works as an OR operator because the sum is FALSE
(0) only when both operands are zero (assuming non-negative numbers).
For example,

=SUM((A1:A10="A")*(B1:B10="B"))

counts the number of times that A exists in A1:A10 AND B exists in
B1:B10. Similarly, you can get the number of times that A exists in
A1:A10 and either B or C exists in B1:B10 with

=SUM((A1:A10="A")*(((B1:B10="B")+(B1:B10="C"))0))

Other logical operations can be created by combining addition and
multiplication. For example, an XOR (A = true or B = true but not
both) operation testing whether A1:A10 = "A", B1:B10 = "B" but not
both can be written as

=SUM(--((A1:A10="A")+(B1:B10="B")=1))

A NAND operation (anything but both A and B true) is simply

=SUM(--((A1:A10="A")+(B1:B10="B")<2))

With several levels of nesting, you can create quite complicated
logical functions.

Your particular logical formula can written as

=SUM((A1:A10=1)*(B1:B10=2)*((C1:C10=1)*(C1:C10<=3 )))

See also the section "Logical Operations With Array Formulas" at
http://www.cpearson.com/excel/ArrayFormulas.aspx .

Note that all the formulas here are array formulas and must be entered
with CTRL SHIFT ENTER.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Thu, 13 May 2010 15:47:32 -0700 (PDT), Matthew
wrote:

Help please, this is more of a theoretical question.

Can you use and + or statements within an array formula.

if I have 3 columns a,b,&c and in these colums I have a list of
integers < 10.

If i wanted to answer the question
Count the incidents where A=1, B=2 AND C=1 OR 2 OR 3
I named the columns a_ b_ and c_ for the sake of ease.


I particularly need to know if I can use an array formula or
sumproduct

My guess of
=SUMPRODUCT((a_=1)*(AND(b_=2,OR(c_=1,c_=2,c_=3))) *1)

did not work with an exapmle that had 1 sollution

nor did
=SUM(IF(a_=1,IF(AND(b_=2,OR(c_=1,c_=2,c_=3)),1,0) ,0))


I think I may have a basic thing wrong but just can not see what it
is.

I know I can solve this a different way but I want to know how to do
it this way.

Thanks in advance for your usual collective help.

Matthew