I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell, I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael

=IF(COUNTIF(Sheet1!L14:Q14,5)+COUNTIF(Sheet1!L14:Q 14,"false")=6,"x","")

--
Gary''s Student - gsnu200777


"MichaelZ" wrote:

I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell, I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael

Try this:

Assuming "false" is a TEXT entry.

=IF(OR(COUNTIF(Sheet1!L14:Q14,"false*")=6,COUNTIF( Sheet1!L14:Q14,5)=6),"X","")

Or, this array formula** :

=IF(OR(AND(Sheet1!L14:Q14="false"),AND(Sheet1!L14: Q14=5)),"X","")

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

--
Biff
Microsoft Excel MVP


"MichaelZ" wrote in message
...
I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell,
I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael

Hmmm...

I may have misunderstood...

My interpretation is *all* 6 cells must be 5 or *all* 6 cells must be false.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

Assuming "false" is a TEXT entry.

=IF(OR(COUNTIF(Sheet1!L14:Q14,"false*")=6,COUNTIF( Sheet1!L14:Q14,5)=6),"X","")

Or, this array formula** :

=IF(OR(AND(Sheet1!L14:Q14="false"),AND(Sheet1!L14: Q14=5)),"X","")

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

--
Biff
Microsoft Excel MVP


"MichaelZ" wrote in message
...
I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell,
I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael

COUNTIF(Sheet1!L14:Q14,"false")

This is a good opportunity to point out one of Excel's quirks.

The above *will not* count *TEXT* entries "false" It will count boolean
FALSE. In fact, both of these will *only* count boolean FALSE:

COUNTIF(Sheet1!L14:Q14,"false")
COUNTIF(Sheet1!L14:Q14,FALSE)

To count *TEXT* "false" you have to coerce it to recognize that it's TEXT
you want to count:

COUNTIF(Sheet1!L14:Q14,"false*")


--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
=IF(COUNTIF(Sheet1!L14:Q14,5)+COUNTIF(Sheet1!L14:Q 14,"false")=6,"x","")

--
Gary''s Student - gsnu200777


"MichaelZ" wrote:

I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible
values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell,
I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael

Just to clarify....
*all* 6 cells must be either 5 or false.
M.

"T. Valko" wrote:

Hmmm...

I may have misunderstood...

My interpretation is *all* 6 cells must be 5 or *all* 6 cells must be false.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

Assuming "false" is a TEXT entry.

=IF(OR(COUNTIF(Sheet1!L14:Q14,"false*")=6,COUNTIF( Sheet1!L14:Q14,5)=6),"X","")

Or, this array formula** :

=IF(OR(AND(Sheet1!L14:Q14="false"),AND(Sheet1!L14: Q14=5)),"X","")

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

--
Biff
Microsoft Excel MVP


"MichaelZ" wrote in message
...
I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell,
I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael

Thanks for the info....I will remember this one.
--
Gary''s Student - gsnu200777


"T. Valko" wrote:

COUNTIF(Sheet1!L14:Q14,"false")

This is a good opportunity to point out one of Excel's quirks.

The above *will not* count *TEXT* entries "false" It will count boolean
FALSE. In fact, both of these will *only* count boolean FALSE:

COUNTIF(Sheet1!L14:Q14,"false")
COUNTIF(Sheet1!L14:Q14,FALSE)

To count *TEXT* "false" you have to coerce it to recognize that it's TEXT
you want to count:

COUNTIF(Sheet1!L14:Q14,"false*")


--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
=IF(COUNTIF(Sheet1!L14:Q14,5)+COUNTIF(Sheet1!L14:Q 14,"false")=6,"x","")

--
Gary''s Student - gsnu200777


"MichaelZ" wrote:

I have a row of cells (i.e., L14:Q14) on Worksheet 1 whose possible
values
are either: 0, 1, 3, 5, "false". On Worksheet 2, for a particular cell,
I'd
like to return a value of "X" if the values in row 14 of Worksheet 1 are
either a "5" or "false" (note, they have to all be either a 5 or false),
otherwise, if they are not all either a "5" or "false" then I'd like to
return a blank, " " on Worksheet 2.

Thanks in advance for any advice.

Michael