Use of IF with arrays
 

I am using the IF operator to test for the presence of a text string value in
an array. My results indicate the formula is testing only the first cell in
the array. How can I get the formula to evaluate each cell in the array?

My formula:
=IF(G6:G15="MAPPED TO:","MAPPED TO:","NEED TO MAP:")

The Microsoft Help documentation of IF states: "If any of the arguments to
IF are arrays (array: Used to build single formulas that produce multiple
results or that operate on a group of arguments that are arranged in rows and
columns. An array range shares a common formula; an array constant is a group
of constants used as an argument.), every element of the array is evaluated
when the IF statement is carried out."

Use of IF with arrays
 

hope I follow you

to count the number of occurrences of "MAPPED TO:" in G6:G15 array
use:

=SUM(IF(G6:G15="MAPPED TO:",1,))
(array-enter it = CTRL+SHIFT+ENTER, instead of using ENTER only)

Use of IF with arrays
 

Try it like this:

=IF(COUNTIF(G6:G15,"MAPPED TO:")=ROWS(G6:G15),"MAPPED TO:","NEED TO MAP:")

Or, this array** version:

=IF(AND(G6:G15="MAPPED TO:"),"MAPPED TO:","NEED TO MAP:")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Andrew L" <Andrew wrote in message
...
I am using the IF operator to test for the presence of a text string value
in
an array. My results indicate the formula is testing only the first cell
in
the array. How can I get the formula to evaluate each cell in the array?

My formula:
=IF(G6:G15="MAPPED TO:","MAPPED TO:","NEED TO MAP:")

The Microsoft Help documentation of IF states: "If any of the arguments to
IF are arrays (array: Used to build single formulas that produce multiple
results or that operate on a group of arguments that are arranged in rows
and
columns. An array range shares a common formula; an array constant is a
group
of constants used as an argument.), every element of the array is
evaluated
when the IF statement is carried out."

Use of IF with arrays
 

I had the CNTL,SHIFT, ENTER part already but my problem was resolved using
your second suggestion. I inserted the AND the parentheses around
G6:G15="MAPPED TO:" and I now get the correct results. Thank you.

- Andrew L


"T. Valko" wrote:

Try it like this:

=IF(COUNTIF(G6:G15,"MAPPED TO:")=ROWS(G6:G15),"MAPPED TO:","NEED TO MAP:")

Or, this array** version:

=IF(AND(G6:G15="MAPPED TO:"),"MAPPED TO:","NEED TO MAP:")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Andrew L" <Andrew wrote in message
...
I am using the IF operator to test for the presence of a text string value
in
an array. My results indicate the formula is testing only the first cell
in
the array. How can I get the formula to evaluate each cell in the array?

My formula:
=IF(G6:G15="MAPPED TO:","MAPPED TO:","NEED TO MAP:")

The Microsoft Help documentation of IF states: "If any of the arguments to
IF are arrays (array: Used to build single formulas that produce multiple
results or that operate on a group of arguments that are arranged in rows
and
columns. An array range shares a common formula; an array constant is a
group
of constants used as an argument.), every element of the array is
evaluated
when the IF statement is carried out."

Use of IF with arrays
 

I used one of T. Valko's solutions. I wasn't trying to count occurrences. I
was trying to set the value of a cell based on the occurrence of a text
string appearing in any one of the cells of the array. It could be done in
two steps using the COUNTIF in the first step but I simply had to add the AND
operator ans some parentheses to make my formula work. Thanks for responding.
--
- Andrew L


"Jarek Kujawa" wrote:

hope I follow you

to count the number of occurrences of "MAPPED TO:" in G6:G15 array
use:

=SUM(IF(G6:G15="MAPPED TO:",1,))
(array-enter it = CTRL+SHIFT+ENTER, instead of using ENTER only)

Use of IF with arrays
 

Ok, but let's make sure this is doing what you think it's doing.

The array formula is testing to make sure *all* cells in the range G6:G15 =
MAPPED TO:.

If only some cells contain MAPPED TO: it is possible to get the result of
MAPPED TO: depending on where the formula is entered.

If your intention was to test that *any* cell in the range = MAPPED TO:,
then try this one:

=IF(COUNTIF(G6:G15,"MAPPED TO:"),"MAPPED TO:","NEED TO MAP:")


--
Biff
Microsoft Excel MVP


"Andrew L" wrote in message
...
I had the CNTL,SHIFT, ENTER part already but my problem was resolved using
your second suggestion. I inserted the AND the parentheses around
G6:G15="MAPPED TO:" and I now get the correct results. Thank you.

- Andrew L


"T. Valko" wrote:

Try it like this:

=IF(COUNTIF(G6:G15,"MAPPED TO:")=ROWS(G6:G15),"MAPPED TO:","NEED TO
MAP:")

Or, this array** version:

=IF(AND(G6:G15="MAPPED TO:"),"MAPPED TO:","NEED TO MAP:")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Andrew L" <Andrew wrote in message
...
I am using the IF operator to test for the presence of a text string
value
in
an array. My results indicate the formula is testing only the first
cell
in
the array. How can I get the formula to evaluate each cell in the
array?

My formula:
=IF(G6:G15="MAPPED TO:","MAPPED TO:","NEED TO MAP:")

The Microsoft Help documentation of IF states: "If any of the arguments
to
IF are arrays (array: Used to build single formulas that produce
multiple
results or that operate on a group of arguments that are arranged in
rows
and
columns. An array range shares a common formula; an array constant is a
group
of constants used as an argument.), every element of the array is
evaluated
when the IF statement is carried out."