View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 2,118
Default 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