Array: {=IF(SUM(IF(O9:V9"A",1/COUNTIF(O9:V9,O9:V9)))=1,"ok",

[Andy D 2002]

hi
this array formula =IF(SUM(IF(O9:V9<>"A",1/COUNTIF(O9:V9,O9:V9)))=1,"ok","not ok")

works very well, it returns "not ok" if there are differences in cells o9 to v9 (excluding cells containing "A")

if i want to exclude 2 other values too how do i modify this
thanks
Andy

[Ivan F Moala]

On 2002-05-16 04:48, Andy D 2002 wrote:
hi
this array formula =IF(SUM(IF(O9:V9<>"A",1/COUNTIF(O9:V9,O9:V9)))=1,"ok","not ok")

works very well, it returns "not ok" if there are differences in cells o9 to v9 (excluding cells containing "A")

if i want to exclude 2 other values too how do i modify this
thanks
Andy

Well Aladins not here so try this;

=IF(SUM(IF((O9:V9<>"A")*(O9:V9<>"B")*(O9:V9<>"C"),1/COUNTIF(O9:V9,O9:V9)))=1,"ok","not ok")

Where your 2 new values to exclude are B & C

[Aladin Akyurek]

this array formula =IF(SUM(IF(O9:V9<>"A",1/COUNTIF(O9:V9,O9:V9)))=1,"ok","not ok")

works very well, it returns "not ok" if there are differences in cells o9 to v9 (excluding cells containing "A")

That's darn right.

if i want to exclude 2 other values too how do i modify this

In another thread, the same question, in disguise of course, also showed up about at the same time as yours. Additional values that you want to exclude requires OR'ing. The Boolean OR, which is +, cannot be used in this formula.

Took me long before I finally remembered my logic: Rewrite OR in terms of NOT and AND.

This is the result, which must be array-entered of course.

=IF(SUM(IF(NOT(O9:V9="A")*NOT(O9:V9="B"),1/COUNTIF(O9:V9,O9:V9)))=1,"ok","not ok")

where "B" is another value that the formula must exclude.

Aladin

Edited in order to align it with your ranges .


[ This Message was edited by: Aladin Akyurek on 2002-05-16 11:58 ]

[Ivan F Moala]

Hey Aladin...Mine works as Well

[Aladin Akyurek]

On 2002-05-16 18:24, Ivan F Moala wrote:
Hey Aladin...Mine works as Well


Of course. I was definitely not around, not just physically.

Aladin