Thanks for the response, Roger !

I shall crawl away into a quiet corner and sulk <vbg
hey, pl come back real soon as you're always needed around here ..
and don't hog that space as the quiet corner's also used by me from time to
time <bg
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote in message
...
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