Hi Roger,

=SUMPRODUCT(($N$7:$N$8=LEFT($H34,8))*(RIGHT($N$7:$ T$7)=RIGHT($H34))*($N8:$T8))

How could your formula be amended, especially for the last term: $N8:$T8
to make it flexible to grab the correct range from within N8:T20 for the
summation
irrespective of what H34 may house.

Eg: should H34 contain say: 01066003 0001 instead, the formula has to grab:
N10:T10

Volatility aside <g, I thought that was the flexibility given by the OFFSET
in my version
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote in message
...
Hi Jim

Yes I would expect it to be able to use either as the arrays are all in
the same plane.
Max is using 2 arrays
A Horizontal array of values using Offset calculated from a Match on the
left part of J34
98123.45, 98097.79, 150,123.76, 0.00, 1,000.00, 15,000.00

A Horizontal array of 1 or 0 from the match on row 7 of the right part of
J34.

The formula you tried with the double unary (and mine with the "*") is
using 3 arrays

A vertical array of 0 or 1 determined which row in column N Matches the
left part of J34
(we used N7:N8, Max used N8:N20) the size of that dimension is not
important in either case
A Horizontal array of the values
A Horizontal array of 0 or 1 to determine the match on row 7 of the right
part of J34

I prefer my solution as if does not involve the use of the volatile Offset
function, but then I would say that wouldn't I? <vbg.

--
Regards

Roger Govier