| 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
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 |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
|
#10
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 |
| 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) | |||
Hybrid Mode
