Checking ALL values in a range

[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

 

[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

 

[Guest]

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

 

[JE McGimpsey]

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

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

 

[Guest]

That's brilliant, thanks a lot.

 

[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 A26 e.g. a 4X4 area
containing the numbers 1 through 16?

Thanks a lot

 

[Aladin Akyurek]

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

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

Or

=SUMPRODUCT((A2:A6<>"")/COUNTIF(A26,A26&""),--ISNUMBER(MATCH(A26,{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}.

 

[Dave Peterson]

A25???

Another version:

=SUMPRODUCT(--(COUNTIF(A25,ROW(INDIRECT("1:16")))>0))

 

[Guest]

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

 

[Jack Sons]

JE,

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

Jack Sons
The Netherlands

 

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

 

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

 

[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

 

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