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.

JE,

Right, I now saw what went wrong. I also noticed that it is not necessary to
array enter.

How should the formula be modified if it must work for al larger range than
only the cells with the correct entries. I tried to put in A2:F3 in stead of
A2:F2 but then it says FALSE. Why? You wrote "COUNT() counts the number of
numeric values returned and ignores the #N/A errors", so if all the correct
values are allready in A2:F2 why has the formula problems with other cells?

What I am looking for is a formula that shows TRUE if in the used range each
element of the {.....} part is found at least once. I tried
=COUNT(MATCH({2,5,234,"a",-10,"dce"},A2:F3,0))=6 with A3:F3 blank
but to no avail. It seems that MATCH() returns 6 times #N/A for the A3:F3
part of the range and nothing for the A2:F2 part.
Please help me out again.

Jack.


"JE McGimpsey" schreef in bericht
...
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.