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
|