I think the issue here Peo, is *actual* location as opposed to *relative*
location.

Since Gary's OP mentioned Match(), which does return relative, I believe
everyone is thinking *relative*.

=SUMPRODUCT((A6:A10=B1)*(ROW(1:5)))

will return "3", when the data in A6:A10 is exactly the same as the data in
the OP, which would be the return you would expect from a formula using
Match().

I think you and Lars and Ashish are talking apples and oranges.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
It will count from row 1 so if you have values from A6 to A10 and if B1 is
blank and if
A7 is blank all other formulas (yours included) will return 2 since it is
the 2nd cell in the range whereas this will return 7 which is correct if
you
count from row 1 but you would need to offset it by the 5 cells above A6
to
get the same result as the other formulas

--


Regards,


Peo Sjoblom

"Lars-Åke Aspelin" wrote in message
...
On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Åke