Array formula with and/or statements

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

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
 
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))
 
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))
Click to expand...

Thnak you Both, they work a treat,
A great help !
 
Thnak you Both, they work a treat,
A great help !
Click to expand...

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
 
<<<"is it possible to return the integer from that column>>>"

I'll assume you have a typo in your follow up question, and you want the
*string* returned from the fourth column,
*AND*
if there are multiple matches in the first 3 columns,
you want multiple strings returned from the 4th column.

Say the strings are in D1 :D27,
enter this *array* formula in E1:

=INDEX(D$1:D$27,SMALL(IF((A$1:A$27=1)*(B$1:B$27=2)*(C$1:C$27={1,2,3}),ROW($1:$27)),ROWS($1:1)))

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.


*After* the CSE entry, copy down as many rows as you anticipate you may have
matches.

When you run out of matches, you'll receive the #NUM! error.
That error will tell you that you have all possible matches returned.
If no error is displayed, there's always the chance that you didn't copy the
formula down enough rows to show *all* answers.

If you don't want to see the #NUM! errors, there is an error trap I can add
....
ALSO, when revising the formula, note that the ROW($1:$27) function does
*not* refer to the row numbers containing the data.
It refers to the *amount of rows* of data.
If your data was contained in say G7 to J41, then the function would be
ROW($1:$35),
since there would be 35 rows of data.
 
<<<"is it possible to return the integer from that column>>>"

I'll assume you have a typo in your follow up question, and you want the
*string* returned from the fourth column,
*AND*
if there are multiple matches in the first 3 columns,
you want multiple strings returned from the 4th column.

Say the strings are in D1 :D27,
enter this *array* formula in E1:

=INDEX(D$1:D$27,SMALL(IF((A$1:A$27=1)*(B$1:B$27=2)*(C$1:C$27={1,2,3}),ROW($1:$27)),ROWS($1:1)))

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, insteadof
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.  Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as many rows as you anticipate you may have
matches.

When you run out of matches, you'll receive the #NUM! error.
That error will tell you that you have all possible matches returned.
If no error is displayed, there's always the chance that you didn't copy the
formula down enough rows to show *all* answers.

If you don't want to see the #NUM! errors, there is an error trap I can add
...
ALSO, when revising the formula, note that the ROW($1:$27) function does
*not* refer to the row numbers containing the data.
It refers to the *amount of rows* of data.
If your data was contained in say G7 to J41, then the function would be
ROW($1:$35),
since there would be 35 rows of data.
Click to expand...

RD

That is almost poetry. A massive thankyou.
The error trap I can manage.

Mathew
 
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
 
Appreciate the feed-back.

As an "added note", the formula returns the text in the "top-to-bottom"
order as they appear in the original dataset.

It's possible to *reverse* this order, if necessary, by simply changing the
Small() function to the Large() function.
--
Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------
<<<"is it possible to return the integer from that column>>>"

I'll assume you have a typo in your follow up question, and you want the
*string* returned from the fourth column,
*AND*
if there are multiple matches in the first 3 columns,
you want multiple strings returned from the 4th column.

Say the strings are in D1 :D27,
enter this *array* formula in E1:

=INDEX(D$1:D$27,SMALL(IF((A$1:A$27=1)*(B$1:B$27=2)*(C$1:C$27={1,2,3}),ROW($1:$27)),ROWS($1:1)))

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
of
the regular <Enter>, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as many rows as you anticipate you may
have
matches.

When you run out of matches, you'll receive the #NUM! error.
That error will tell you that you have all possible matches returned.
If no error is displayed, there's always the chance that you didn't copy
the
formula down enough rows to show *all* answers.

If you don't want to see the #NUM! errors, there is an error trap I can
add
...
ALSO, when revising the formula, note that the ROW($1:$27) function does
*not* refer to the row numbers containing the data.
It refers to the *amount of rows* of data.
If your data was contained in say G7 to J41, then the function would be
ROW($1:$35),
since there would be 35 rows of data.
Click to expand...

RD

That is almost poetry. A massive thankyou.
The error trap I can manage.

Mathew
 
Back
Top