If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Sum within an Index Match Statement
I am using an Index Match within a file that could have multiple row (but not
column) based matches. How can I adjust the formula to Sum all the results instead of just showing the first one? Formula currently: =IF(ISERROR(INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 PLAN'!$1:$1,0))),0,INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 PLAN'!$1:$1,0))). Month Template A16 is a category of product that could actually be listed more then once within Column B of the 2007 Plan tab. G1 in the Month Template is an actual month (i.e. Jan 2007) but it will never appear more then once in Row 1 of the 2007 Plan tab. I want to sum all the category matches within the 2007 Plan tab after the Index Match does its part. Thank you in advance. Regards, Mike 
Ads 
#2




Sum within an Index Match Statement
I don't believe you need the INDEX,MATCH combination for the sum you are
looking for. Try something like this: SUMPRODUCT(('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23)*('2007 PLAN'!$C$1:$DY$1='Month Template'!$G$1)*('2007 PLAN'!$C$2:$DY$10000)) This part: ('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23) returns TRUE for Col_B items that match 'Month Template'!$A23 This part: ('2007 PLAN'!$C$1:$DY$1='Month Template'!$G$1) returns TRUE for Row_1 items that match 'Month Template'!$G$1 When both conditions are TRUE.... the corresponding intersections from ('2007 PLAN'!$C$2:$DY$10000) will be summed. NOTE: You may need to tweak the references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP "Mike The Newb" wrote: > I am using an Index Match within a file that could have multiple row (but not > column) based matches. How can I adjust the formula to Sum all the results > instead of just showing the first one? > > Formula currently: > > =IF(ISERROR(INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > PLAN'!$1:$1,0))),0,INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > PLAN'!$1:$1,0))). > > Month Template A16 is a category of product that could actually be listed > more then once within Column B of the 2007 Plan tab. G1 in the Month Template > is an actual month (i.e. Jan 2007) but it will never appear more then once in > Row 1 of the 2007 Plan tab. I want to sum all the category matches within the > 2007 Plan tab after the Index Match does its part. > > Thank you in advance. > > Regards, > > Mike 
#3




Sum within an Index Match Statement
Ron,
Thank you for responding! I think I see where you're going with this but unfortunately my result is #Value!. I can see how each part is seeking to find its match but I guess I'm confused by the " * " being used being used in between the sections of the formula  most likely because I didn't explain my self properly. 2007 Plan Tab Column B  category names (can and will appear more then once) Row 1  months (never repeated) The " * " is called out twice in your formula and yet there really isn't any multiplication involved. It's like a SumIf would work but I don't want to hard "hard code" the column to sum because it will change as the months progress and the value in G1 of the Month Template changes. It's almost like an "if" is needed in your formula  like if/when the intersection of category and month is found sum the values in the column that matches the month as it contains that value per category/month for that month. What purpose does the " * " serve? Thanks in advance. Regards, Mike "Ron Coderre" wrote: > I don't believe you need the INDEX,MATCH combination for the sum you are > looking for. > > Try something like this: > SUMPRODUCT(('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23)*('2007 > PLAN'!$C$1:$DY$1='Month Template'!$G$1)*('2007 PLAN'!$C$2:$DY$10000)) > > This part: ('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23) > returns TRUE for Col_B items that match 'Month Template'!$A23 > > This part: ('2007 PLAN'!$C$1:$DY$1='Month Template'!$G$1) > returns TRUE for Row_1 items that match 'Month Template'!$G$1 > > When both conditions are TRUE.... > the corresponding intersections from ('2007 PLAN'!$C$2:$DY$10000) > will be summed. > > NOTE: You may need to tweak the references to suit your situation. > > Does that help? > > *********** > Regards, > Ron > > XL2002, WinXP > > > "Mike The Newb" wrote: > > > I am using an Index Match within a file that could have multiple row (but not > > column) based matches. How can I adjust the formula to Sum all the results > > instead of just showing the first one? > > > > Formula currently: > > > > =IF(ISERROR(INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > > PLAN'!$1:$1,0))),0,INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > > PLAN'!$1:$1,0))). > > > > Month Template A16 is a category of product that could actually be listed > > more then once within Column B of the 2007 Plan tab. G1 in the Month Template > > is an actual month (i.e. Jan 2007) but it will never appear more then once in > > Row 1 of the 2007 Plan tab. I want to sum all the category matches within the > > 2007 Plan tab after the Index Match does its part. > > > > Thank you in advance. > > > > Regards, > > > > Mike 
#4




Sum within an Index Match Statement
Hi, Mike
Actually, there IS multiplication occuring...Let's see how I do with the explanation. Try this quick example on a blank sheet to see how it works: Put these values in A1:C3 A 5 10 B 1 2 C 20 30 Next A4: =SUMPRODUCT((A2:A3="C")*(B1:C1=5)*(B2:C3)) Note: A4 will return 20 Explanation: (A2:A3="C") returns FALSE, TRUE (B1:C1=5) returns TRUE, FALSE When those segments are multiplied: (A2:A3="C")*(B1:C1=5) Excel coerces TRUE's to 1's and FALSE's to 0's and creates a 2x2 grid with those values FALSE*FALSE=FALSE, FALSE*FALSE=FALSE TRUE*TRUE=TRUE, FALSE*FALSE=FALSE which becomes 0*0=0,0*0=0 1*1=1,0*0=0 which then becoms 0,0 1,0 When multiplied against the values: 0,0 1,0 times 1,2 20,30 The results are 0,0 20,0 and the SUMPRODUCT is 20 (0+0+20+0) See?....The cells at the intersection where Col_A="C" and Row_1=5 are added. Post back with any questions. Does that help? *********** Regards, Ron XL2002, WinXP "Mike The Newb" wrote: > Ron, > > Thank you for responding! > > I think I see where you're going with this but unfortunately my result is > #Value!. I can see how each part is seeking to find its match but I guess I'm > confused by the " * " being used being used in between the sections of the > formula  most likely because I didn't explain my self properly. > > 2007 Plan Tab > Column B  category names (can and will appear more then once) > Row 1  months (never repeated) > > The " * " is called out twice in your formula and yet there really isn't any > multiplication involved. It's like a SumIf would work but I don't want to > hard "hard code" the column to sum because it will change as the months > progress and the value in G1 of the Month Template changes. It's almost like > an "if" is needed in your formula  like if/when the intersection of category > and month is found sum the values in the column that matches the month as it > contains that value per category/month for that month. What purpose does the > " * " serve? > > Thanks in advance. > > Regards, > Mike > > > "Ron Coderre" wrote: > > > I don't believe you need the INDEX,MATCH combination for the sum you are > > looking for. > > > > Try something like this: > > SUMPRODUCT(('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23)*('2007 > > PLAN'!$C$1:$DY$1='Month Template'!$G$1)*('2007 PLAN'!$C$2:$DY$10000)) > > > > This part: ('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23) > > returns TRUE for Col_B items that match 'Month Template'!$A23 > > > > This part: ('2007 PLAN'!$C$1:$DY$1='Month Template'!$G$1) > > returns TRUE for Row_1 items that match 'Month Template'!$G$1 > > > > When both conditions are TRUE.... > > the corresponding intersections from ('2007 PLAN'!$C$2:$DY$10000) > > will be summed. > > > > NOTE: You may need to tweak the references to suit your situation. > > > > Does that help? > > > > *********** > > Regards, > > Ron > > > > XL2002, WinXP > > > > > > "Mike The Newb" wrote: > > > > > I am using an Index Match within a file that could have multiple row (but not > > > column) based matches. How can I adjust the formula to Sum all the results > > > instead of just showing the first one? > > > > > > Formula currently: > > > > > > =IF(ISERROR(INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > > > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > > > PLAN'!$1:$1,0))),0,INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > > > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > > > PLAN'!$1:$1,0))). > > > > > > Month Template A16 is a category of product that could actually be listed > > > more then once within Column B of the 2007 Plan tab. G1 in the Month Template > > > is an actual month (i.e. Jan 2007) but it will never appear more then once in > > > Row 1 of the 2007 Plan tab. I want to sum all the category matches within the > > > 2007 Plan tab after the Index Match does its part. > > > > > > Thank you in advance. > > > > > > Regards, > > > > > > Mike 
#5




Sum within an Index Match Statement
Ron  excellent  thank you. Very concise yet easy to understand. I had to
mess around with my core data to get it to work  but playing around with your sample formula aided me greatly in the "clean up". Regards, Mike "Ron Coderre" wrote: > Hi, Mike > > Actually, there IS multiplication occuring...Let's see how I do with the > explanation. > > Try this quick example on a blank sheet to see how it works: > Put these values in A1:C3 > A 5 10 > B 1 2 > C 20 30 > > Next > A4: =SUMPRODUCT((A2:A3="C")*(B1:C1=5)*(B2:C3)) > > Note: A4 will return 20 > > Explanation: > (A2:A3="C") returns FALSE, TRUE > (B1:C1=5) returns TRUE, FALSE > > When those segments are multiplied: (A2:A3="C")*(B1:C1=5) > Excel coerces TRUE's to 1's and FALSE's to 0's > and creates a 2x2 grid with those values > > FALSE*FALSE=FALSE, FALSE*FALSE=FALSE > TRUE*TRUE=TRUE, FALSE*FALSE=FALSE > > which becomes > 0*0=0,0*0=0 > 1*1=1,0*0=0 > > which then becoms > 0,0 > 1,0 > > When multiplied against the values: > 0,0 > 1,0 > times > 1,2 > 20,30 > > The results are > 0,0 > 20,0 > > and the SUMPRODUCT is 20 (0+0+20+0) > > See?....The cells at the intersection > where Col_A="C" and Row_1=5 > are added. > > Post back with any questions. > > Does that help? > *********** > Regards, > Ron > > XL2002, WinXP > > > "Mike The Newb" wrote: > > > Ron, > > > > Thank you for responding! > > > > I think I see where you're going with this but unfortunately my result is > > #Value!. I can see how each part is seeking to find its match but I guess I'm > > confused by the " * " being used being used in between the sections of the > > formula  most likely because I didn't explain my self properly. > > > > 2007 Plan Tab > > Column B  category names (can and will appear more then once) > > Row 1  months (never repeated) > > > > The " * " is called out twice in your formula and yet there really isn't any > > multiplication involved. It's like a SumIf would work but I don't want to > > hard "hard code" the column to sum because it will change as the months > > progress and the value in G1 of the Month Template changes. It's almost like > > an "if" is needed in your formula  like if/when the intersection of category > > and month is found sum the values in the column that matches the month as it > > contains that value per category/month for that month. What purpose does the > > " * " serve? > > > > Thanks in advance. > > > > Regards, > > Mike > > > > > > "Ron Coderre" wrote: > > > > > I don't believe you need the INDEX,MATCH combination for the sum you are > > > looking for. > > > > > > Try something like this: > > > SUMPRODUCT(('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23)*('2007 > > > PLAN'!$C$1:$DY$1='Month Template'!$G$1)*('2007 PLAN'!$C$2:$DY$10000)) > > > > > > This part: ('2007 PLAN'!$B$2:$B$65536='Month Template'!$A23) > > > returns TRUE for Col_B items that match 'Month Template'!$A23 > > > > > > This part: ('2007 PLAN'!$C$1:$DY$1='Month Template'!$G$1) > > > returns TRUE for Row_1 items that match 'Month Template'!$G$1 > > > > > > When both conditions are TRUE.... > > > the corresponding intersections from ('2007 PLAN'!$C$2:$DY$10000) > > > will be summed. > > > > > > NOTE: You may need to tweak the references to suit your situation. > > > > > > Does that help? > > > > > > *********** > > > Regards, > > > Ron > > > > > > XL2002, WinXP > > > > > > > > > "Mike The Newb" wrote: > > > > > > > I am using an Index Match within a file that could have multiple row (but not > > > > column) based matches. How can I adjust the formula to Sum all the results > > > > instead of just showing the first one? > > > > > > > > Formula currently: > > > > > > > > =IF(ISERROR(INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > > > > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > > > > PLAN'!$1:$1,0))),0,INDEX('2007 PLAN'!$A$1:$DY$10000,MATCH('Month > > > > Template'!$A23,'2007 PLAN'!$B:$B,0),MATCH('Month Template'!$G$1,'2007 > > > > PLAN'!$1:$1,0))). > > > > > > > > Month Template A16 is a category of product that could actually be listed > > > > more then once within Column B of the 2007 Plan tab. G1 in the Month Template > > > > is an actual month (i.e. Jan 2007) but it will never appear more then once in > > > > Row 1 of the 2007 Plan tab. I want to sum all the category matches within the > > > > 2007 Plan tab after the Index Match does its part. > > > > > > > > Thank you in advance. > > > > > > > > Regards, > > > > > > > > Mike 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Returning MULTIPLE values with Index and Match  Fly  Excel Discussion (Misc queries)  1  June 1st 06 05:50 PM 
Index Match Help Needed Badly  [email protected]  Excel Discussion (Misc queries)  4  May 8th 06 02:45 AM 
Index and Match  the next step  MoonWeazel  Excel Worksheet Functions  5  October 27th 05 05:43 PM 
Match Index  cjjoo  Excel Worksheet Functions  3  October 25th 05 09:33 AM 
Match, Index, Vlookup, Large....Help Please  hothotm[email protected]  Excel Worksheet Functions  3  December 15th 04 02:38 PM 