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."

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)

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."

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."

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)

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."