Home 
Search 
Today's Posts 
#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 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Returning MULTIPLE values with Index and Match  Excel Discussion (Misc queries)  
Index Match Help Needed Badly  Excel Discussion (Misc queries)  
Index and Match  the next step  Excel Worksheet Functions  
Match Index  Excel Worksheet Functions  
Match, Index, Vlookup, Large....Help Please  Excel Worksheet Functions 