| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT clue needed | Excel Discussion (Misc queries) | |||
| Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
