Formula Help!: Index/Match to return a range to use in Sumpr

ajmckenna

Board Regular
Joined
Oct 7, 2002
Messages
145
I need to multiply & sum my data in Row 4 x either row one (B1:E1) or row 2 (B2:B4) with result in Cell F6; I would like to do a lookup based on value in cell A6 to return the the appropriate range to use in the sumproduct formula. I figured I could use multiple if statements as I showed below. but would like to use a Index/Match and sumproduct as I will have more than the 2 options in "mysumproduct range 1" below
Sumproduct range 1(input table)
A B C D E F
Row 1 S1 .25 .25 .10 .40
Row 2 C1 .25 .50 .15 .10

Sumproduct range 2
Row 4 10 20 30 40

Row 6 S1
Formula in F6 = if($A6="S1",Sumproduct($B1:$E$1,B4:E4),if(A6="C1",Sumproduct($B2:$E$2,B4:E4),"error"))
(note F6 will be copied to down & to the right)
I would like to use and Index/Match to lookup the proper range based on the value placed in A6. My input table will not be sorted in acending order so I believe Lookup will not work. Is this possible? and can I use index match to return a range to use in the sumproduct?

Thanks guys!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
I kept getting a ref! error with

=SUMPRODUCT(INDEX($B$1:$E$2,MATCH(A6,$A$1:$A$2,0)),$B$4:$E$4)

but until a correct answer is posted on this, you should be able to get away with
=SUMPRODUCT($B$4:$E$4,OFFSET($B$1:$E$1,MATCH(A6,$A$1:$A$2,0)-1,0))

I think the index version would run a little smoother.
This message was edited by IML on 2002-11-01 10:38
 

paelinck

Board Regular
Joined
Mar 19, 2002
Messages
68
This is how I would solve your problem:
Book1
ABCDEFG
1S10.250.250.10.40
2C10.250.50.150.11
3
410203040
5
6C121
Sheet1


The formulas in G1 and G2 puts a "1" next to the row that corresponds with the selection in cell A6.

Please not that the formula in B6 is an array-formula. Do not type the braces around the formula and close the formula with Ctrl-Shift-Enter.

If you want to include new rows below row 2, copy down the formula in column G next to the new row(s) and extend the range B1:E2 in the formula of B6 to include the new row(s)

I hope my explanation is clear enough.

Marc
 

paelinck

Board Regular
Joined
Mar 19, 2002
Messages
68
Sorry, I forgot something in the last paragraph. This should be:

If you want to include new rows below row 2, copy down the formula in column G next to the new row(s) and extend the range B1:E2 in the formula of B6 to include the new row(s)
*and* extend the range G1:G2 in the same formula to include the new row(s) as well.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743

ADVERTISEMENT

=SUMPRODUCT($B$4:$E$4,OFFSET($B$1:$E$1,MATCH(A6,$A$1:$A$2,0)-1,0))

This is the only item that would need to be changed. Adjust a2 to A6 for 6 rows, etc. This assumes your $B$4:$e$4 would change to something else to do the insertion of row.
 

ajmckenna

Board Regular
Joined
Oct 7, 2002
Messages
145
Thanks Marc:

but the formula will be rolling so that I need to be able to copy the formula in B6 so that it will take the previous I.e B4:E4 will need to change to C4:f4 if I copy the formaula to the right.

Also, I will be using this formula for multiple lines I.e row 7 might use the S1 valuesnot the C1 values and therefore the formula in G1 will not work

I think IML's solution will work fine. The actual s1 and C2 input table will be in another workbook so hopefully it still works

thanks for all the help.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-01 11:30, ajmckenna wrote:
[...]
I think IML's solution will work fine. The actual s1 and C2 input table will be in another workbook so hopefully it still works
[..]

The INDEX version is...

=SUMPRODUCT(INDEX($B$1:$E$2,MATCH(A6,$A$1:$A$2,0),0),$B$4:$E$4)

as Ian also wanted you to have.
 

Forum statistics

Threads
1,144,312
Messages
5,723,651
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top