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 !

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