Hi Jim

I think the reason the double unary minus didn't work in this scenario,
is because we are using a mixture of Vertical and Horizontal arrays -at
least, that has been my experience.
Had both arrays been vertical, then the double unary would have worked
equally as well.

--
Regards

Roger Govier


"Jim May" wrote in message
...
Thanks Roger; Previously I was trying:

=SUMPRODUCT(--($N$7:$N$8=LEFT(H33,8)),--(RIGHT($N$7:$T$7)=RIGHT($H33)),$N8:$T8)

But was getting the #VALUE! Result

Still not sure why that didn't work; I'll take your suggestion!!
Thanks again,

Jim


"Roger Govier" wrote:

Hi Jim

Even though you have used an array formula, Index, Match will only
pick
up the first occurrence of 1 as the RIGHT() character of cells in
N7:T7

Try instead the non-array entered formula

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

--
Regards

Roger Govier


"Jim May" wrote in message
...
I have a table: below in Range N&:T8

809-0 809-1 809-2 810-0 810-1 810-2
01066001 98,123.45 98,097.79 150,123.76 0.00 1,000.00 15,000.00
01066002
01066003

From the above I need to Sum
with
H34 = 01066001 0001
In J34 I have
{=SUM(INDEX($N$7:$T$8,MATCH(LEFT($H34,8),$N$7:$N$8 ,0),MATCH(RIGHT($H34,1),RIGHT($N$7:$T$7,1),0)))}

But J34 Is only picking up the $98,097.79, not the s/b $99,097.79.
Can someone spot my problem?
Thanks In Advance,
Jim