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
|