Hi Max
You are correct, but let me explain.
I did all of my testing on a different range altogether from Jim, to
make it easier to see all the data at the same time.
My actual formula was
=SUMPRODUCT(($A$1:$A$8=LEFT(J1,8))*(RIGHT($B$1:$G$ 1)=RIGHT(J1))*(B2:G2))
where you will notice the last term is relative.
In translating to match Jim's ranges, I inadvertently copied his $N's
and made the formula absolute and typed
=SUMPRODUCT(($N$7:$N$8=LEFT($H34,8))*(RIGHT($N$7:$ T$7)=RIGHT($H34))*($N8:$T8))
I had assumed, probably wrongly, that he was only trying to sum one row
of data, based upon the value in $H34
and that $H35 might have contained 01066002 0001, $H36 01066003 0001
etc. so as the formula was copied down it would take successive rows.
Clearly my error of typing Absolutes for N8:T8 would have prevented
this, but in my testing where I had 01066002 0001 in cell J2, 01066003
0001 in J3 and I had copied down, I had achieved correct results.
Equally, extending the range to N8:T20 as you mention, will not work
because we are then trying to multiply arrays of differing dimensions
which upon evaluation will result in #N/A errors.
Your solution, which uses Offset to return a single array of numbers to
be multiplied by an equal sized array of 0's and 1's gives the correct
solution in all scenarios and is far superior.
A definite case where volatility is clearly outweighed by flexibility.
I shall crawl away into a quiet corner and sulk<vbg
--
Regards
Roger Govier
"Max" wrote in message
...
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