A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Sum within an Index Match Statement



 
 
Thread Tools Display Modes
  #1  
Old January 8th 07, 05:39 PM posted to microsoft.public.excel.misc
Mike The Newb
external usenet poster
 
Posts: 22
Default 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  
Old January 8th 07, 06:16 PM posted to microsoft.public.excel.misc
Ron Coderre
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

  #3  
Old January 8th 07, 07:08 PM posted to microsoft.public.excel.misc
Mike The Newb
external usenet poster
 
Posts: 22
Default 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  
Old January 8th 07, 07:46 PM posted to microsoft.public.excel.misc
Ron Coderre
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

  #5  
Old January 8th 07, 09:46 PM posted to microsoft.public.excel.misc
Mike The Newb
external usenet poster
 
Posts: 22
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 [email protected] Excel Worksheet Functions 3 December 15th 04 01:38 PM


All times are GMT +1. The time now is 07:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.