Harlan,

Thanks for the explanation...
"compares every cell in rng against the first cell in rng"
is what I didn't grasp.
But as I said before, I think I will go back to VBA.<g

Regards,
Jim Cone


"Harlan Grove" wrote in message oups.com...
Jim Cone wrote...
Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
....

First your formula. F13:J13 is a 1D range, 1 row by 5 columns.
INDEX(F13:J13,1,0) is also a 1D range consisting of the 1st row and all
columns of F13:J13, which is identical to F13:J13. So your
(F13:J13)=INDEX(F13:J13,1,0) compares F13:J13 to itself. I'd be very
surprised if this didn't result in a 1D array of 5 TRUEs. So your
SUMPRODUCT call is equivalent to the simpler COLUMNS(F13:J13), and your
formula as a whole is equivalent to the array formula
=COLUMNS(F13:J13)=COUNTA(F13:J13).

My formula,

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

compares every cell in rng against the first cell in rng, which is
always given by INDEX(rng,1,1) even if rng is multiple area. If all
cells in rng are the same (and nonblank), this will equal the number of
cells in rng. The COUNTA call will equal the number of nonblank cells
in rng. If all cells in rng evaluate to the same thing and are
nonblank, the SUMPRODUCT call will equal the COUNTA call.

Another way to have done this would have been

=SUMPRODUCT((1-ISBLANK(rng))/COUNTIF(rng,rng&""))=1

but it's longer and involves an O(N^2) COUNTIF call. Then again, it'd
be shorter still to use the array formula

=VAR(MATCH(rng,rng,0))=0

but the MATCH call is also O(N^2).