I'm trying to create a formula which checks that a range of cells e.g. A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each number is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

Thanks

One way, though not scaleable:

=IF(COUNTIF(A2:F2,1)0,1,0)+IF(COUNTIF(A2:F2,2)0, 1,0)+IF(COUNTIF(A2:F2,3)0,1,0)+IF(COUNTIF(A2:F2,4 )0,1,0)+IF(COUNTIF(A2:F2,5)0,1,0)+IF(COUNTIF(A2: F2,6)0,1,0)

Regards

Trevor


"nospaminlich" wrote in message
...
I'm trying to create a formula which checks that a range of cells e.g.
A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each number
is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

Thanks

Thanks a lot Trevor. I might have to go for this option but I'm really
looking for a more scaleable solution .

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

In article ,
nospaminlich wrote:

I'm trying to create a formula which checks that a range of cells e.g. A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each number is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

That's brilliant, thanks a lot.

Just a thought...

I've adapted this so I can use it for columns as well as rows but is it
possible to set it so it covers a range of say A2:D6 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot

=SUMPRODUCT((A2:A6<"")/COUNTIF(A2:D6,A2:D6&""),--ISNUMBER(MATCH(A2:D6,ConditionRange,0)))=COUNT(Con ditionRange)

where ConditionRange refers to a range housing the values whose
existence in A2:D6 you want to check.

Or

=SUMPRODUCT((A2:A6<"")/COUNTIF(A2:D6,A2:D6&""),--ISNUMBER(MATCH(A2:D6,{1,2,3,4,5,6},0)))=6

if you refer to the values of interest by means of a constant array like
{1,2,3,4,5,6}.

nospaminlich wrote:
Just a thought...

I've adapted this so I can use it for columns as well as rows but is it
possible to set it so it covers a range of say A2:D6 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot

A2:D5???

Another version:

=SUMPRODUCT(--(COUNTIF(A2:D5,ROW(INDIRECT("1:16")))0))



nospaminlich wrote:

Just a thought...

I've adapted this so I can use it for columns as well as rows but is it
possible to set it so it covers a range of say A2:D6 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot

--

Dave Peterson

The SUMPRODUCT solutions work fine. Excellent, thanks a lot.

JE,

I can't figure out how it works exactly. Be so kind as to explain.
TIA

Jack Sons
The Netherlands

"JE McGimpsey" schreef in bericht
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

In article ,
nospaminlich wrote:

I'm trying to create a formula which checks that a range of cells e.g.
A2:F2
contain all the values 1,2,3,4,5,6 irrespective of which cell each
number is
in.

Unfortunately, everything I've tried has failed so any help would be
appreciated.

ROW(INDIRECT("1:6"))

returns an array of numbers {1,2,3,4,5,6}

MATCH() compares these numbers to the values in A2:F2. If the number is
found, a numeric index is returned, if not, #N/A is returned.

COUNT() counts the number of numeric values returned and ignores the
#N/A errors.

The =6 checks to see that 6 numeric values were returned. If any #N/As
are returned, the comparison will fail.


In article ,
"Jack Sons" wrote:

I can't figure out how it works exactly. Be so kind as to explain.
TIA

Jack Sons
The Netherlands

"JE McGimpsey" schreef in bericht
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

JE,

Thanks for your explanation.

What if the 6 elements of the range A2:F2 are not the neat 1 to 6 but, say,
2, 5, 234, a, -10 and dce? Must {2,5,234,"a",-10,"dce"} be put in the place
of ROW(INDIRECT("1:6"))?
I tried, I entered and array entered, but the formula in both cases results
in zero. Please help me further.

Jack.

"JE McGimpsey" schreef in bericht
...
ROW(INDIRECT("1:6"))

returns an array of numbers {1,2,3,4,5,6}

MATCH() compares these numbers to the values in A2:F2. If the number is
found, a numeric index is returned, if not, #N/A is returned.

COUNT() counts the number of numeric values returned and ignores the
#N/A errors.

The =6 checks to see that 6 numeric values were returned. If any #N/As
are returned, the comparison will fail.


In article ,
"Jack Sons" wrote:

I can't figure out how it works exactly. Be so kind as to explain.
TIA

Jack Sons
The Netherlands

"JE McGimpsey" schreef in bericht
...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=COUNT(MATCH(ROW(INDIRECT("1:6")),A2:F2,0))=6

Substituting your array in place of ROW(INDIRECT("1:6")) and
array-entering the result works fine for me:

=COUNT(MATCH({2,5,234,"a",-10,"dce"},A2:F2,0))=6


In article ,
"Jack Sons" wrote:

What if the 6 elements of the range A2:F2 are not the neat 1 to 6 but, say,
2, 5, 234, a, -10 and dce? Must {2,5,234,"a",-10,"dce"} be put in the place
of ROW(INDIRECT("1:6"))?
I tried, I entered and array entered, but the formula in both cases results
in zero. Please help me further.