View Single Post
  #4   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

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