Array formula with and/or statements
 

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

Array formula with and/or statements
 

Here's a couple more...

=SUMPRODUCT(--(A_=1),--(B_=2),--(ISNUMBER(MATCH(C_,{1,2,3},0))))

=SUMPRODUCT(--(A_=1),--(B_=2),(C_=1)+(C_=2)+(C_=3))

--
Biff
Microsoft Excel MVP


"Matthew" wrote in message
...
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

Array formula with and/or statements
 

On 14 May, 02:38, "T. Valko" wrote:
Here's a couple more...

=SUMPRODUCT(--(A_=1),--(B_=2),--(ISNUMBER(MATCH(C_,{1,2,3},0))))

=SUMPRODUCT(--(A_=1),--(B_=2),(C_=1)+(C_=2)+(C_=3))

--
Biff
Microsoft Excel MVP

"Matthew" wrote in message

...

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

Thnak you Both, they work a treat,
A great help !

Array formula with and/or statements
 

On 14 May, 06:44, Matthew wrote:
On 14 May, 02:38, "T. Valko" wrote:



Here's a couple more...

=SUMPRODUCT(--(A_=1),--(B_=2),--(ISNUMBER(MATCH(C_,{1,2,3},0))))

=SUMPRODUCT(--(A_=1),--(B_=2),(C_=1)+(C_=2)+(C_=3))

--
Biff
Microsoft Excel MVP

"Matthew" wrote in message

...

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

Thnak you Both, they work a treat,
A great help !

Again thak you for the previous answer it helped a lot. Especialy as I
clearly had no idea on syntax.

1 Other question,

give the first statement, if there was a fourth column that contained
a string not an integer is it possible to return the integer from that
column if the first 3 satisfy the question and how would you handle it
if there were multiple answers. Again without resorting to helper
columns and vlookups etc.

Regards

Matthew

Array formula with and/or statements
 

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