Proper function for task.

[MichaelZ]

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

 

[Gary''s Student]

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

 

[T. Valko]

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)

 

[T. Valko]

Hmmm...

I may have misunderstood...

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

 

[T. Valko]

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*")

 

[MichaelZ]

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

 

[Gary''s Student]

Thanks for the info....I will remember this one.