Two arrays same?
 

What formula could tell A1:J1 = A10:J10?

Two arrays same?
 

Try one of these:

=SUMPRODUCT(--(A1:J1=A10:J10))=10

Or, array entered** :

=AND(A1:J1=A10:J10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
What formula could tell A1:J1 = A10:J10?

Two arrays same?
 

Hello,

Biff already provided two solutions.

But if you need to be a little bit more tolerant with regards to text
& numbers, for example if the text 2 should be treated as being
identical to the number 2 then use
=SUMPRODUCT(--(EXACT(A1:J1,A10:J10)))=10

Regards,
Bernd

Two arrays same?
 

I would write the SUMPRODUCT formula this way...

=SUMPRODUCT(--(A1:J1<A10:J10))=0

That way, you will not have to remember to adjust the 10 to reflect the
number of cells in a range (in case this formula gets used elsewhere on
ranges containing different numbers of cells).

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try one of these:

=SUMPRODUCT(--(A1:J1=A10:J10))=10

Or, array entered** :

=AND(A1:J1=A10:J10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
What formula could tell A1:J1 = A10:J10?

Two arrays same?
 

You could do this and still maintain the intuitive logic (are the cells
*equal*):

=SUMPRODUCT(--(A1:J1=A10:J10))=COLUMNS(A1:J1)

My preference would be the array formula.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
I would write the SUMPRODUCT formula this way...

=SUMPRODUCT(--(A1:J1<A10:J10))=0

That way, you will not have to remember to adjust the 10 to reflect the
number of cells in a range (in case this formula gets used elsewhere on
ranges containing different numbers of cells).

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try one of these:

=SUMPRODUCT(--(A1:J1=A10:J10))=10

Or, array entered** :

=AND(A1:J1=A10:J10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
What formula could tell A1:J1 = A10:J10?