Help with Control Array Sum Formula
 

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

Help with Control Array Sum Formula
 

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

Help with Control Array Sum Formula
 

Another option ..

Placed in J34:
=SUMPRODUCT(OFFSET($O$7:$T$7,MATCH(LEFT(H34,8),$N$ 8:$N$20,0),)*(RIGHT($O$7:$T$7,1)=RIGHT(H34,1)))

Adjust the range: $N$8:$N$20
to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jim May" wrote:
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

Help with Control Array Sum Formula
 

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

Help with Control Array Sum Formula
 

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

Help with Control Array Sum Formula
 

Thanks for the additional info/comment;
Jim

"Roger Govier" wrote:

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

Help with Control Array Sum Formula
 

Roger - FYI !!!
The formula MAX used can be modified to accept the "--" thing.
Jim


"Roger Govier" wrote:

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

Help with Control Array Sum Formula
 

Cooooooooool, Max for Another option ..
Thanks,
Jim

"Max" wrote:

Another option ..

Placed in J34:
=SUMPRODUCT(OFFSET($O$7:$T$7,MATCH(LEFT(H34,8),$N$ 8:$N$20,0),)*(RIGHT($O$7:$T$7,1)=RIGHT(H34,1)))

Adjust the range: $N$8:$N$20
to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jim May" wrote:
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

Help with Control Array Sum Formula
 

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


"Jim May" wrote in message
...
Roger - FYI !!!
The formula MAX used can be modified to accept the "--" thing.
Jim


"Roger Govier" wrote:

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

Help with Control Array Sum Formula
 

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

Help with Control Array Sum Formula
 

Jim, you're welcome.

Albeit volatile, the OFFSET part of it does provide flexibility in grabbing
the correct range within cols O to T depending on the input in H34
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jim May" wrote in message
...
Cooooooooool, Max for Another option ..
Thanks,
Jim

Help with Control Array Sum Formula
 

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

Help with Control Array Sum Formula
 

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