Checking ALL values in a range

G

Guest

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
 
T

Trevor Shuttleworth

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
 
G

Guest

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

JE McGimpsey

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

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

Guest

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
 
A

Aladin Akyurek

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

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}.
 
D

Dave Peterson

A2:D5???

Another version:

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

Jack Sons

JE,

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

Jack Sons
The Netherlands
 
J

JE McGimpsey

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.
 
J

Jack Sons

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.
 
J

JE McGimpsey

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
 
J

Jack Sons

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.
 
Top